compare two queries

Question

How can i compare query X and Y and say that X is better then Y when they both take almost the same time in small cases scenarios???

the problem is that i have 2 queries that are suposed to run on a very big database, so run a evaluate is quite not a option (aka try and fail method), so we created a small database to perform some tests... and now the evaluating wich query is the best is a problem since on our test base they run almost the same time: 5 min so... besides the time taken to return, what is another way to measure how good a query is???

Answer 1

Have you examined the query plans? If the queries are returning the same data and are taking the same amount of time to execute, my guess is that the query plans will be nearly identical meaning that there is no meaningful difference between the two queries.

Also, have you taken into account that queries perform differently as the database size changes?

I'm wondering if you are prematurely optimizing the code. In my mind, if I have a query that works and is understandable, I can address performance issues through indexes. And that is usually easier than changing the queries to improve performance.

Answer 2

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 proc plan cache
DBCC FREEPROCCACHE -- clear data 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.

Answer 3

SET STATISTICS IO ON
SET STATISTICS TIME ON

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

Answer 4

Evaluating query performance on a significantly different data set generally makes very little sense. Query plans and their efficiency can vary greatly depending on the data stats.

So to get any realistic estimates, you need a database as close to the "real" one as possible. Best of all, take a copy of your "big database" and tune your queries to it.