Every once in a while my client Indevin need to re-calculate the vessel compositions across the winery using the application I wrote, and because each composition relies on the one before it, this means starting at job 1 and working its way through to the last job creating a composition for each vessel after every job. As you can understand this can take a long time with over 70,000 jobs and 3000 vessels on the system and its only going to get bigger!
To save time the system saves up writing out to the SQL database until it has about 500KB of inserts, then it uses the bulk insert feature to write the multiple records in one go, this is seriously quicker than individual insert statements, and is done similar to the code below (I use MyDAC from devart which implements tLines for the sql text property)
...
sql.lines.add('INSERT INTO composition (values) DATA(data);');
if sql.lines.count>5000 then
execute(sql.text);
...
Its never been that quick but recently we introduced additive lot code tracking into the composition file to speed up tracking and tracing for auditing purposes, this has increased the size of the compositions to over 4 million records and once the changes were done I re-ran the compositions and had to wait 6-8 hours for it to run. totally unacceptable to anyone!
I wont bore you with the two days it took to find the problem, which was caused by the SQL’s TLines property, an analysis shown it was taking about 6mS to insert each line. WTF!
Changing the insert to
var s:string; ... s:=s+'INSERT INTO compositions (values) DATA(data)'+#10; ... if length(s)>500000 then execute(s);
Presto! Compositions now run in 20 minutes.
The moral of the post is do not use any Tline property inside any sort of inner loop!