how to test the performance of 2 queries

Question

how to test the performance of 2 queries

erics44

SSCertifiable

Points: 6311
More actions
May 12, 2012 at 12:43 am

#260753

Hi
I have 2 queries and I want to see which performs better.
What's the best way of doing this?
I could just run them both in the query analyser or is there a better way?
Thanks in advance

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Matthew Darwin SSCertifiable Points: 5513 More actions · Answer 1

You can try using the estimated query plan with both queries in te same batch, that will show a percentage of the batch that each query takes in terms of cost.

You could also try using STATISTICS TIME and STATISTICS IO when you run the queries to see which is performing better.

Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 2

Slight modification...

You can try using the estimated / actual (preferred) query plan with both queries in the same batch, that will show a percentage of the batch that each query takes in terms of cost.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Matthew Darwin (5/12/2012)
You can try using the estimated query plan with both queries in te same batch, that will show a percentage of the batch that each query takes in terms of cost.

Just bear in mind that the percentages are based on estimates and estimates can be wrong. I can trivially find an example where the query that shows as 1% of the batch is hundreds of times slower than the one that shows as 99%.

My preferred way is to run both queries multiple times (10 or so), use profiler to record the execution characteristics and average the stats (discarding the first execution's stats)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Matthew Darwin SSCertifiable Points: 5513 More actions · Answer 4

Whoops meant to include the actual plan in there.

The only reason I sometimes start with the estimated plan is in my environment I frequently have to work with ad hoc queries from an analyst team that simply do not run in an acceptable time, let alone over ten times. The estimated plan can often quickly highlight missing join predicates etc quicker than trying to decipher their code!

Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Doesn't make the slightest difference if you use estimated or actual plan for the relative %. They'll be the same across the two plans and they are still estimates and are still prone to being wrong.

The only thing that the actual plan gets you that the estimated does not is the actual row counts and actual execution counts. The estimates used to generate the % are generated at compile time.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Matthew Darwin SSCertifiable Points: 5513 More actions · Answer 6

That's a fair point; though I guess you'll also quickly see if there are major estimate issues there from the discrepancy between estimated row counts and actuals? Is that usually down to statistics, or are there other factors that will cause a "bad" plan?

Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Row counts is one thing that causes estimation errors, but it's not the only thing. UDFs are another. There are more.

Lots of things other than stats. See if you can find and download my 24 Hours of PASS presentation 'Bad Plan! Sit'.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Matthew Darwin SSCertifiable Points: 5513 More actions · Answer 8

Thanks, I'll check it out this afternoon!

Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]

erics44 SSCertifiable Points: 6311 More actions · Answer 9

Thanks for the replies

I'll check out the execution plans

Suresh B. SSC-Insane Points: 22986 More actions · Answer 10

erics44 (5/12/2012)
Thanks for the replies
I'll check out the execution plans

No. Not execution plan. Use Profiler as suggested by Gail.

GilaMonster (5/12/2012)...My preferred way is to run both queries multiple times (10 or so), use profiler to record the execution characteristics and average the stats (discarding the first execution's stats)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 11

As a sidebar, also be leary of SET STATISTICS TIME ON when user defined functions are involved. A query may take sub second times when the setting is off and 20 or 30 seconds when the setting is on.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)