Accounting for Network When Comparing SQL Run Times

When you are executing SQL statements to find which one is the best, you need to take into consideration the factors that can skew the accuracy of the results of the testing. These factors include caching the data, caching the indexes, caching the SQL statement, other activities on the CPU, and network traffic. Quest SQL Optimizer provides option settings that allow you to minimize these factors so that you can find the SQL statement that is best for your database environment. This tip discusses eliminating the effect of network traffic when you are comparing SQL run times. The previous two tips discuss how to minimize the effect of caching and how to minimize the affect of other activities on the CPU.

Eliminating the Effect of Network Traffic

When comparing the run times of two SQL statements, you want to know which SQL statement is going to execute best in your environment. Although network traffic certainly may affect the performance of a SELECT SQL statement, when you are optimizing a SQL statement through rewriting the SQL syntax and adding optimization hints, your goal is eliminate any factors that would skew the comparison of the run times of the alternative SQL statements.

Since no matter which alternative SQL statement you choose when tuning a SQL statement, you will still have the same amount of data to pass over the network. And if the network is slow for one SQL statement and faster for another, including the data transfer time in the run time could skew the comparison of the run times of the SQL alternatives against the original SQL statement.

In Quest SQL Optimizer, you have the choice to execute the SQL statements on the database server only which does not return the data to the client. Or, you can execute it so that when a SELECT SQL statement is executed, the data is returned to the client, although it is not displayed.  

Run on server

The Run on server option found under the Optimize SQL | Optimizer| Batch Run | Execution Method calculates the run time of a SQL statement from the server?s clock time when the SQL statements starts to execute on the CPU to the time it finishes without sending the data to the client computer. Executing the SQL statement under this option provides you with how long it takes to execute on the database server?s CPU. This is the option selected by default.

Note: For Oracle, your logon account must have the privilege to execute the SYS.DBMS_SQL package to retrieve the run time from the server.

Run on client

The Run on client option executes the SQL statement and returns the data to the client. Executing the SQL statement under this option provides you with the run time that includes the time it takes to transfer the data to the client computer.

With this option you can limit the number of rows that are retrieved using the Limit rows retrieved to option. The default number of rows that are returned using this option is 100. If you enable this option, it is important to note that this is not comparing the way the SQL statement will actually be executed in the application since you are not retrieving all of the data; it is only good for online queries that browse the first few pages of data. The execution run times will likely be different when you retrieve all the rows.

You can also specify the number of rows that will be retrieved at one time when fetching the data from the server using the Number of rows returned in a single network transfer option.

When SELECT SQL statements are executed, a comparison of the result is done to further insure that result set for an alternative SQL statement is the same as the original SQL statement. When you select the Run on Server option, the comparison made between the original SQL statement and the SQL alternatives is the number of row returned. No comparison of the result data is performed.

>Result Comparison

When SELECT SQL statements are executed, a comparison of the result is done to further illustrate that result set for an alternative SQL statement is the same as the original SQL statement. When you select the Run on server option, the comparison made between the original SQL statement and the SQL alternative is the number of rows returned. No comparison of the result data is performed.

When SELECT SQL statements are executed using the Run on client option, the comparison is done between the hash values of the data. To compare the result sets of the original and alternative SQL statements, each row of the result sets is hashed and then the hash values are stored in the memory of the client compute. The values from the result set of the original SQL statement are compared to the values from each SQL alternative to illustrate that the result sets are the same. The data is not stored in memory nor is it stored on the disk drive of the client computer.

For some SQL statements without an ORDER clause, the alternative SQL statement’s execution plan may not retrieve the records in the same order as the original SQL statement. So when you look at the results, you need to take into account that it is the same result set even though it may not be in the same order.