Convert Code Carefully

One of the biggest sources of slow running code is code that has been converted from running on a Microsoft Jet database (.MDB) and is now running against ODBC data. One of the worst of these is code that loops through a table modifying single records at a time instead of using a single pass-through Structured Query Language (SQL) statement to do the same operation.

File-based databases, such as Microsoft Jet's .MDB files, are relatively efficient at these operations, and so this code may run at an acceptable speed against an .MDB file when compared with the SQL statement approach. The same code, however, is likely to be extremely slow against ODBC data because of the large amount of network traffic involved versus sending a single SQL statement to the server.

Other operations that may help converted code run faster include:

• Cache multiple inserts locally and update the server in one operation.

• Place transactions around multiple updates to help data integrity and ease programming complexity.

• Use parameterized queries to reduce query compilation and execution time.

More information on using these techniques is provided below.