Query Performance, you be the judge!

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)

Query Performance, you be the judge!

Author

Topic

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2007-04-26 : 11:03:30

Trying to figure out if there is a significant difference in performance between the 2 queries. Query #2 will make table maintenance MUCH MUCH easier.

I think its obvious that having a subquery is going to slow things down correct? In the Execution Plan estimation tool in EM it shows an extra hash stage for Query #2.

-- Query #1
--
select *
from Table1
where code like '1.1.%'

-- Query #2
--
select *
from Table1
where codeID in ( select codeid from codeLookup where code like '1.1.%')

For some reason when I changed Query #2 by adding the top 3000 to the subquery, the query time increased to 3 seconds (it was previously 1 second):

select *
from Table1
where codeID in ( select top 3000 codeid from codeLookup where code like '1.1.%')

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-26 : 11:26:38

I prefer Inner Join or EXISTS operator over IN operator.

select *
from Table1 t1 JOIN CodeLookup cl
on t1.codeID = cl.codeID and cl.code like '1.1.%'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-04-26 : 11:29:01

note that exists and IN aren't the same thing.
their logic differs and this must be accounted for.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2007-04-26 : 12:04:14

Is there a different in the inner join?

select *
from Table1 t1 JOIN CodeLookup cl
on t1.codeID = cl.codeID

where cl.code like '1.1.%'

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-04-26 : 13:45:45

quote:
Originally posted by sql777

Is there a different in the inner join?

select *
from Table1 t1 JOIN CodeLookup cl
on t1.codeID = cl.codeID

where cl.code like '1.1.%'

INNER JOIN Should work just fine. But, the thing to check out is the performance of the query (not just the time to return a result). I'm not 100% against using IN as it has its uses, but there are some situations that can cause grief and it is good to be waware of them. Check out this link for some more information about IN vs EXISTS vs JOIN: [url]http://sqlservercode.blogspot.com/2007/04/you-should-never-use-in-in-sql-to-join.html[/url]

-Ryan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources