Comparing Queries in Query Analyzer

Often times there are several different ways to write a SQL query to return a desired result set. Some times the ordering of JOINs and WHERE predicates or the approach taken in the query (nested queries vs. joins for example) can make a big difference on query performance. You won't really notice a difference on smaller databases, but on queries which search thousands or millions of records you may see a big difference in performance simply by changing the order of where predicates. So the question becomes, when given two or more queries which return the same results, how do you determine which one performs best?

Well if you are using MS SQL Server just copy the queries into the same Query Analyzer window, turn on the Show Execution Plan (Ctrl+K), and run the queries (F5). Once the queries execute go to the Execution Plan tab and note their relative costs. For example if you run two queries you may have results which look something like:

Query 1: Query cost (relative to batch): %60.00
.
.
.
Query 2: Query cost (relative to batch): %40.00
.
.
.

In this case you would want to go with Query 2 as it has the lowest cost relative to the batch.

Another thing I should point out here is that some times you will notice that two seemingly "different" queries have the exact same cost relative to the batch. If you look at the execution plan you may notice that the query optimizer generated the exact same execution plan for these queries. In these cases it may not make much of a difference which query you choose. (This happened a me a couple of weeks ago. I had 4 different versions of a query, each of which was optimized to the same execution plan by SQL Server's optimizer.)

Anyway, I'm sharing this little Query Analyzer tip because I discovered that not a lot of CF developers are aware of this feature, or at least not as many as I though would be. Also, I'm relatively new to SQL Server so feel free to chime in with any other Query Analyzer tips you may have.

BlogCFC was created by Raymond Camden. This blog is running version 5.8.001.