How to compare two queries?

User7354632781 picture User7354632781 · Sep 21, 2010 · Viewed 23.5k times · Source

How can I compare two queries X and Y and say that X is better than Y, when they both take almost the same time in small cases scenarios?

The problem is that I have two queries that are supposed to run on a very big database, so run and evaluate is not quite an option. Therefore, we created a small database to perform some tests. Evaluating which query is better is a problem, since on our test base, they run in almost the same time (about 5 minutes). Besides the time taken to return, what is another way to measure how good a query is?

Answer

Joe Stefanelli picture Joe Stefanelli · Sep 21, 2010
SET STATISTICS IO ON
SET STATISTICS TIME ON

Run the queries and compare logical reads for the various tables and execution times.