Testing SQL Alternatives That Contain Variables

This tip covers executing SQL statements that require you to enter a parameter before the SQL statements are executed.

A SQL statement with a variable which is provided at run time poses an extra challenge when trying to determine which alternative SQL statement is going to give the best overall performance. Each time the SQL statement is execute, a different variable could be supplied.

You may find that when you use one variable, alternative 1 has the best elapsed time and when you use another variable, maybe alternative 2 has the best elapsed time. So, how can you determine which SQL statement to use in the application?

This diagram shows two SQL statements (SQL1 and SQL2) plus the performance time and the retrieved data volume relationship.  

If you look at the performance of the SQL statements to the left of the Meet Point (the point where the two lines intersect), you can see that when the variable that is used produces a lower data volume that falls into the range to the left of the Meet Point, SQL 2 has the best performance.

So in this example, let’s say that the first variable that you test falls into the left range of Meet Point, and therefore SQL2 gives better performance than SQL1. Then the second variable that you test selects more data and falls into the data volume range that is to the right of the Meet Point. In this second test, SQL1 provides the best performance. The big challenge when doing performance tuning for a SQL statement which requires a variable is that there is no single winner between SQL1 and SQL2 for entire data set.

Two factors should be considered when choosing the SQL statement for your application. The first criterion that you should use to pick the “best” SQL statement is the best fit for most of the variables that are likely to be used when this SQL statement is executed. Another selection criterion is that there should not be a rapid rise in the performance time after the Meet Point. For this example, SQL1 would be a better overall choice rather than SQL2, since there is a rapid growth on the right-hand side of the Meet Point for SQL2. SQL1 will provide a more consistent performance throughout the entire data set.