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.

Michael Sharman's Gravatar nice tip, I suppose you could also do that same thing with stored procedures (from Query Analyser)


exec sp_myStoredProc1 [args]

exec sp_myStoredProc2 [args]

Note that in MSSQL 2005 the Query Analyzer is gone, replaced with the Query Editor which is cool because it shows not only the execution plan but also the 'actual' plan!. You can also save the plan as an XML document :)

# Posted By Michael Sharman | 10/12/06 6:47 PM
Dwain Browne's Gravatar thanks for the tip man, i was wondering about that.. :)
# Posted By Dwain Browne | 4/29/07 2:04 PM
BlogCFC was created by Raymond Camden. This blog is running version 5.8.001.