Script Optimizations

Well, I just finished updating a script that was very inefficient for work. It is true that form and optimization come after function, but neither should be neglected long-term.

Originally the script was written to display a list of documents (with numerous links and attributes), with anchored headers for each group. The scripts took as input the ID for the section of docs to be listed. The database call was a good efficient call with 'Order By' sorting, three levels deep. But the original author then ignored the doc order and started looping through the docs looking for all the docs that belonged in each header section. Then the next header would be started and the whole list would be looped through again, only picking the other docs that matched that heading. Easily this works out that you must go through the entire record set a number of times equal to the number of headers.

What disasters can be made with a For Next loop surrounding a Do While loop, when a simpe Do While with good logic will suffice.

For a section with 200 documents and 14 headers, that would be 2,800 checks of records and the associated overhead of management. This example took 13,000ms.

The odd thing is that the original author had the records coming out of the query exactly in the right order. All that was needed was more elegant logic to insert the headers in the correct places. Only one pass was needed through the record set, with a simple check to see if the old header matches the new header. If they match, do nothing, if not, insert the new header.

The second update to this script was letting the database run a query against each document number matching a certian list, instead of bring over the whole listing and using the script to check if a match exists. Added overhead is the extra number of db connenctions used, but letting the db process matches (with its indexing functions) is much faster than looping for checks in a script (also not as much memory overhead).

With both of these updates, I was able to trim the processing time all the way down to 3,500ms, nearly 4x the speed of the original script. There are also 20% fewer lines and it is easier to understand (in my opinion).