How to compare two queries?

sql-server query-optimization
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.

Related questions

Counting DISTINCT over multiple columns

Is there a better way of doing a query like this: SELECT COUNT(*) FROM (SELECT DISTINCT DocumentId, DocumentSessionId FROM DocumentOutputItems) AS internalQuery I need to count the number of distinct items from this table but the distinct is over two …

Read More
How to find out what is locking my tables?

I have a SQL table that all of a sudden cannot return data unless I include with (nolock) on the end, which indicates some kind of lock left on my table. I've experimented a bit with sys.dm_tran_locks …

Read More
Finding blocking/locking queries in MS SQL (mssql)

Using sys.dm_os_wait_stats I have identified what I believe is a locking problem wait type waittime pct running ptc LCK_M_RS_S 2238.54 22.14 22.14 LCK_M_S 1980.59 19.59 41.73 Is there a way I can find the top blocking/locking …

Read More