How to tell which is better?

Question

How to tell which is better?

Christopher Stobbs

SSC-Insane

Points: 21098
More actions
October 9, 2007 at 3:52 am

#178816

Hi All,
I have run two queries that are returning the same results which is good.
So I have run some stats to find out which is better in performance :
QUERY ONE
CPU time elapsed = 569ms
Execution time elapsed = 3562
QUERY TWO
CPU time elapsed = 1927ms
Execution time elapsed = 4112
However when I run the execution plan it shows when running both queries in the same batch.
QUERY ONE takes 66 percent of the batch cost
QUERY ONE takes 34 percent of the batch cost
Which data should I use to determine which query is better?
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
SQL-4-Life

Viewing 8 posts - 1 through 7 (of 7 total)

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

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

generally the query which takes most % is less efficient. I rarely look at time when tuning, working on io as the most important measure. Once I've sorted the io I may look at time, but generally if you reduce io so time is also reduced.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Grant Fritchey SSC Guru Points: 398718 More actions · Answer 2

You should look at the number of scans and reads each one is doing. Time is nice & all, but for most queries, reducing scans or reads leads to reductions in times (not always, your mileage may vary, etc.).

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Steve Jones - SSC Editor SSC Guru Points: 734744 More actions · Answer 3

I tend to agree with Colin and Grant, but which is which in the batch %? You have Query One twice.

Jeff Moden SSC Guru Points: 1003940 More actions · Answer 4

If it's the "Estimated" Execution Plan... pay those percentages no mind... it "lies" because it will frequently miss things. For example, only the first pass of a triangular join in a sub-query is considered leaving out all the millions of internal rows that may actually be spawned.

If it's the "Actual" Execution Plan, it's pretty much faithful.

Just to add another opinion, I always consider duration as part of performance tuning... it's usually a pretty good indication as to the number of resources that will need to be consumed by the system, etc. Of course, you also have to consider what will happen to all of that when the scale of tables grows... code that takes a minute today may take hours tomorrow when the table reaches a tipping point. But I've found that, in most cases, when comparing two pieces of known working code, I've found that the one with the shorter duration is also the most effecient for resource consumption.

Ok... time to duck... here comes all the folks that will say "speed doesn't matter" 😉

--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)

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 5

Sorry about that.

Query one takes 66%

Query two takes 34%

thanks for the advice everyone

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

dots67 SSC Veteran Points: 210 More actions · Answer 6

It is useless to compare queries like this.

Duration may vary up to 100 times and more between 'hot' and 'cold' execution. Cold execution is an execution after DBCC DROPCLEANBUFFERS or server restart.

So provide all values: CPU, Reads, Writes and Duration

Jeff Moden SSC Guru Points: 1003940 More actions · Answer 7

No... if you're talking about simple duration measurements, it's not useless... you've just gotta make sure they're either both "hot" or both "cold" 😛

But, in the face of blocking, looking a CPU time and the other measurements you suggested, are much, much better.

--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)