SQL Performance Tuning

It's hard to mention catalog functions without bubbling in fury. Their design is contemptible, their effect is pernicious, they are a confusion and a disgrace. That being said, they are also frequently useful and support for catalog functions is far better than support for selections from standard SQL's INFORMATION_SCHEMA tables.

There is one vital datum that you won't find in any INFORMATION_SCHEMA table and therefore can't determine with any standard SQL:1999 function. We refer to the information provided by the ODBC function SQLSpecialColumns . Any general program needs to know the optimal set of columns that can be used to identify a row in a table uniquely, and knowing the primary key is not necessarily enough. Of course, one should be able to access a table via primary-key data. The trouble is that there may be faster alternatives, such as the ROWID or some other unique key. When the information from SQLSpecialColumns is available, you can do a data change like this:

1. SELECT a row.

2. Display the row contents on the screen, as part of a dialog box in which the user can change any column data, except the special columns.

3. End the current transaction so that all locks are released.

4. Accept the user's changes.

5. If the user is making a change, SELECT the row again to make sure no other user has made a change, then do:

   UPDATE (or DELETE) ... WHERE <special column> = <original unchanged value>

The essential element in this plan is that there must be a quick unique identifier, and that's the information SQLSpecialColumns can provide. (See Chapter 15, "Locks," for a more complete example with an analogous technique.)

The Bottom Line: Catalog Functions

Use ODBC's SQLSpecialColumns function to find the optimal set of columns that can be used to identify a row in a table uniquely. Use those columns in search conditions to improve the performance of data-change statements.