I got some very weird behavior of our faultline database, so hopefully someone here knows what could cause havoc on our database, whenever I try to import FASL scripts. I tried 2 methods to reach my goal (inserting more than ten custom FASL scripts) and came to a point where I think that the database is the problem.
OS: Windows 2008 R2 Standard 64-bit
RAM: 4 GB
CPU: 2x3.00 GHz
SQL Server 2008 R2, ServicePack 2
Installed Version 7.5
Installed FASL-Support with the FSUpdate tool
Import and publish around 400 FASL-Scripts to the MVM database and assign them to a vulnerability by web-interface.
Import well-formed FASL-Files with the utility program FSDBUtil.
FSDBUtil tended to fail in certain cases (although the imported FASL-scripts were well-formed) with a “Database Request Timeout” after around 40 seconds.
Write SQL-Scripts using the predefined stored procedures “Content.Script_Save”, “Content.Vuln_Save” and “Content.Script_Publish”.
FSDBUtil (Method1 ) tends to fail in certain cases (although the imported FASL-scripts were well-formed) with a “Database Request Timeout” after around 40 seconds. Usually this behavior occurs after successfully importing around 10 FASL-scripts. Restarting the MSSQL database service did not fix the problem. I thought maybe FSDBUtil is the problem, which led me to write my own SQL-Scripts (Method 2).
I attached the SQL-scripts for better understanding. During testing and debugging the code, I stumbled upon very strange behavior of the faultline-database, which probably cause problems for FSDBUtil in the first place.
In save_test.sql, I followed following workflow:
As far as I know, launching this SQL-Script for around 10 times works fine. All datasets were generated from faultlineID 6000001 to 6000012 until the execution time skyrocketed to around 40 – 50 minutes for future executions. Debugging showed that Content.Script_Publish caused high latencies by executing following SQL-Statement (Line 76 in Content.Script_Publish):
INSERT INTO Content.Script
, @Now --CreatedDate
, @Now --ModifiedDate
WHERE FaultlineID = @FaultlineID
It seems that an INSERT statement targeting the Content.Script table cause high execution times. Also I realized that a DELETE statement took the same amount of time as soon as there are more than 10 custom fasl scripts in this table (DELETE FROM Content.Script WHERE ScriptID >= 6000000; ). Content.Script contains around 50019 datasets.
I assume that there is some internal mechanism (index, etc.) preventing the database to efficiently inserting and deleting data from Content.Script... SELECT statements work perfectly fine. What would be a workaround to get the FSDBUtil / SQL-Scripts working in a reasonable execution time?