When writing SQL queries we often measure the performance of the query or procedure by the amount of time it takes to complete. Often I found myself saying that if it completes in a couple seconds then it is good. Lately however I have found myself more concerned with the IO count then with the execution time. The IO count is the amount of reads and writes performed by the execution of a query. This value is what can really tell you if your SQL query is optimized.

To view the IO performance of the query one can use the SET STATISTICS command of SYBASE. To enable the display of the logical reads and writes you would turn on the statistics by the command:

SET STATISTICS IO ON

And would turn it off by running the command:

SET STATISTICS IO OFF

Now that you can obtain the io performance of the query you can make use of the SHOWPLAN command to fine tune your query’s performance. The SHOWPLAN command is enabled similar to the SET STATISTICS command by toggling on and off by the ON/OFF keywords:

SET SHOWPLAN ON

By setting the SHOWPLAN command to ON you will be outputted with the steps that are performed by the query or procedure. The output will display all the selects, inserts, and creations that occur when running the sql statement. Using this input one is able to fine tune the query by the reorganization of statements, addition of indexes to remove or reduce Table Scans, as well as further optimization techniques.