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?
SET STATISTICS IO ON
SET STATISTICS TIME ON
Run the queries and compare logical reads for the various tables and execution times.