How do I tell which query is faster ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How do I tell which query is faster ?

Author

Topic

Jusvistin
Yak Posting Veteran

81 Posts

Posted - 2003-08-13 : 14:56:49

I've got the following two queries, and I want to see which one is faster.


select region, name, population
from cia
where name in ('france', 'germany', 'italy')

select region, name, population
from cia
where name = 'france' or
      name = 'germany' or
      name = 'italy'

The way I'm doing it now is by displaying the execution plan tab, running both queries in the same batch, and looking at Query cost (relative to the batch) figures. If one of the queries displays a lower percentage, I assume it's faster.

Is this the way I'm supposed to be doing it, or is there a better way ?

Thanks,
Kevin

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-08-13 : 15:08:58

It's a pretty good way to start, but you do have to bear in mind that the costs displayed are estimates. Their overall accuracy can be strongly dependent on the estimated number of rows generated by each step being accurate.

That said, the execution plans generated from your examples will by identical, since IN (set of literal values) get turned into ORs.

BTW, is this a database of the CIA World Factbook? Is it available anywhere?

Jusvistin
Yak Posting Veteran

81 Posts

Posted - 2003-08-13 : 15:10:52

Thanks Arnold,

Are you saing that after the query has executed, the results displayed in the Execution Plan tab are still estimates, and not reflective of the actual costs to process the request ?

Thanks,
Kevin

Jusvistin
Yak Posting Veteran

81 Posts

Posted - 2003-08-13 : 15:30:24

quote:
Originally posted by Arnold Fribble

It's a pretty good way to start, but you do have to bear in mind that the costs displayed are estimates. Their overall accuracy can be strongly dependent on the estimated number of rows generated by each step being accurate.

That said, the execution plans generated from your examples will by identical, since IN (set of literal values) get turned into ORs.

BTW, is this a database of the CIA World Factbook? Is it available anywhere?

LOL

No, it's some table I pulled off some website somewhere to play around with.

Sure sounds cool though doesn't it.

We've probably got the Office of Homeland Security reading these notes right now.

Kevin

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-08-13 : 15:33:56

The cost values are still estimates, which is annoying. It displays both actual and estimated row counts, though they differ slightly in what they represent. If you look at a plan with a nested loop in it, you'll see that while the estimated plan shows the inner (lower) loop's input with row counts for each outer loop row, the 'actual' plan shows the counts for all outer loop rows.

Jusvistin
Yak Posting Veteran

81 Posts

Posted - 2003-08-13 : 15:38:30

So.....

If I really wanted to compare the speed of two different queries, what's the best way ?

Use a datetime counter at the start and end of each query ?

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-08-13 : 15:43:00

The CIA World Factbook ( www.cia.gov/cia/publications/factbook/ )is quite handy, though I wonder at the usefulness of their comparative area measure: the UK is "slightly smaller than Oregon".

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-08-13 : 15:45:10

SET STATISTICS IO ON
SET STATISTICS TIME ON

are usually quite useful -- they're only decidedly unhelpful when you have user-defined function calls that do their own SELECTs, since those don't get counted (doh!)

Jusvistin
Yak Posting Veteran

81 Posts

Posted - 2003-08-13 : 16:15:19

Thanks Arnold.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources