Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to compare two queries?

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?

like image 204
User7354632781 Avatar asked Sep 21 '10 20:09

User7354632781


People also ask

How do you compare two queries performance?

Run the queries and compare logical reads for the various tables and execution times. @CombatCaptain You can also stack the comparing queries together in SSMS and press CTRL+M (include actual execution plan) and then F5 .

How do you find the difference between two query results in SQL?

The Minus Operator in SQL is used with two SELECT statements. The MINUS operator is used to subtract the result set obtained by first SELECT query from the result set obtained by second SELECT query.


2 Answers

SET STATISTICS IO ON SET STATISTICS TIME ON 

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

like image 79
Joe Stefanelli Avatar answered Oct 26 '22 04:10

Joe Stefanelli


As already mentioned, check the execution plans.

Importantly, compare the 2 queries fairly by clearing the cache down between each run, just to make sure you're not seeing skewed results due to the effect of data already being cached (don't run on production server):

DBCC DROPCLEANBUFFERS -- clear data cache DBCC FREEPROCCACHE -- clear proc plan cache 

Then what I usually do is check the Reads, Writes, CPU and Duration for a comparison.

It's very important that you test with production-level data volumes (and ideally greater to see how it will scale). It's at those volumes that you'll really see any performance difference. Testing with small data volumes could leave you open to problems later on.

like image 31
AdaTheDev Avatar answered Oct 26 '22 06:10

AdaTheDev