Comparing Query Performance

A significant portion of your time as a database programmer or administrator will be to improve the performance of queries. While there are several benchmarks you can compare two or more queries, the actual comparision of the two queries can lean you do make false assumptions. When you run a query, data and execution plans can be cached. This cache will actually work against you in trying to determine which option is best.

So how do you ensure a consistent test environment?

You mush clear out that cache. Simliar to keeping your browser free and clear of useless data, you must also clear out this cache so the second query doesn’t have an advanage over the first.

WARNING: Do not run this in your production environment. It will make your users irritable!

-- The following removes all plans from the cache
-- The following Clears the data cache

Run this separate from, but between the queries. What I mean is, Run the above code, and when it finishes run your first query, take notes on the performance of that query. Then, run the above code again. Then run the second query, again take notes of the performance. Now, you have a more equitable comparison between your queries.

Why do I say more equitable, rather than a truly equal comparison?

Well, there are many other variables at work. If your test environment is shared by you and other developers, someone else could be using the same database objects, at the same time as one of your tests. This could create its own information in cache. It could also cause locks, or other performance drains on your test. Either way, your results could be skewed. Try to obtain an exclusive lock on your database objects during testing.  Secondly, if you are testing on a machine that does more than run SQL, such as testing on localhost, you may get interference from other processes on that machine running, like Burning a CD or running a disk defrag.

Using these two commands in SQL will help minimize one of the most common mistakes in comparing two queries and getting a better result the second time. As always, if you have any questions or comments, please send them in!

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.


Leave a comment

Your email address will not be published. Required fields are marked *