Performance Questions - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Performance Questions

Author

Topic

haoest
Starting Member

8 Posts

Posted - 2008-08-15 : 18:49:52

Hi I have 2 very similar queries. The difference in their performance, however, is extremely huge. Does anybody have any simple explainations?


select a.cityname, a.zipcode, c.cnt, a.statename  from zip_city_mapping a
right join (
      select b.zipcode, count(b.cityname) cnt, b.statename
      from xzip_state_mapping b
      group by b.zipcode, statename
      having count(b.cityname) > 0 -- ZERO here
)c
on a.zipcode = c.zipcode


---------------------------------------------------------------------------

select a.cityname, a.zipcode, c.cnt, a.statename  from zip_city_mapping a
right join (
      select b.zipcode, count(b.cityname) cnt, b.statename
      from xzip_state_mapping b
      group by b.zipcode, statename
      having count(b.cityname) > 1 -- ONE here
)c

on a.zipcode = c.zipcode

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-16 : 01:14:56

What do you see in Execution plan? Any Difference in Cost?

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-08-16 : 01:41:22

how many rows does each return? it takes time to send data over the network - maybe that's the entire difference? the query plans are likely identical.

elsasoft.org

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-08-16 : 07:51:22

Could you trap the Plan via the trace and post

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-16 : 08:41:34

First query returns all zipcodes, no matter the count.
Second query returns only those zipcodes whose count is 2 or more.

So I believe you have few duplicates in the derived table c.

For a fair comparison, remove the grouping part for the first query.

E 12°55'05.25"
N 56°04'39.16"

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-08-17 : 03:24:53

You don't even tell us which one is faster or by how much, so I am assuming first is faster than second.
I think maybe that if b.cityname is not nullable then we (or rather SQL Server) can determine that count(cityname) will always be > 0 without having to do anything. Determining >1 would require additional work on the table before applying the join. It doesn't sound very convincing though :(
What happens if you change the second one to where cnt>1 in the outer?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources