October 19, 2011 at 9:11 am
I just discovered the IN functionality in the where clause to be able to look up multiple conditions. It got me thinking that you could use that much like a join in certain circumstances, but i don't know how efficient that would be or how proper.
For example
SELECT Number, POTicket
FROM PO
WHERE (POTicket IN
(SELECT Serviceticket
FROM TicketMain))
vs
Select Number, POTicket from PO inner join ServiceTicket on POTicket=ServiceTicket
When i ran a execution plan they both cost the same. It seems like when using the IN clause I could also get very specific in my subquery instead of having to do it in the where clause with the join.
SELECT Number, POTicket
FROM PO
WHERE (POTicket IN
(SELECT Serviceticket
FROM TicketMain where status='Closed'))
vs
Select Number, POTicket from PO inner join ServiceTicket on POTicket=ServiceTicket where ServiceTicket.Status='Closed'
How should these be used? And when is it appropriate? Any help would be awesome.
Thanks
October 19, 2011 at 9:13 am
Short answer is that they are not equivalent so you can't compare performance.
Long answer (series) => http://sqlinthewild.co.za/index.php/2010/04/27/in-exists-and-join-a-roundup
October 19, 2011 at 9:18 am
that link seems to be down but i did google based on the information and got this link
http://sqlserverpedia.com/blog/sql-server-bloggers/in-exists-and-join-%E2%80%93-a-roundup/
Where he found:
My conclusion from earlier posts stands. If all you are doing is looking for matching or non-matching rows and you don’t need any columns from the second table, use IN or Exists (or their negations), as appropriate for the situation. Only when you need columns from the second table should Join be used.
Does this seem accurate?
October 19, 2011 at 9:21 am
You can trust her pretty much 1000% on what she writes ;-).
October 19, 2011 at 9:30 am
mbender (10/19/2011)
that link seems to be down
Yeah, my hosting company just suspended my site without warning and are taking their sweet time answering support tickets as to why and when.
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
October 19, 2011 at 9:34 am
No phone support?
October 19, 2011 at 9:38 am
Ninja's_RGR'us (10/19/2011)
No phone support?
Yes, I really want to sit on hold for an hour or 3 paying international phone charges. They have a toll-free number, but it's only toll-free if you're calling from USA or Canada.
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
October 19, 2011 at 9:43 am
GilaMonster (10/19/2011)
Ninja's_RGR'us (10/19/2011)
No phone support?Yes, I really want to sit on hold for an hour or 3 paying international phone charges. They have a toll-free number, but it's only toll-free if you're calling from USA or Canada.
DOH!
October 19, 2011 at 10:43 am
that stinks, but thanks for all you help very informative as usual
October 19, 2011 at 10:44 am
mbender (10/19/2011)
that stinks, but thanks for all you help very informative as usual
It's back online now. So hopefullly no more than 1-2 millions users got shut out ;-).
October 19, 2011 at 10:46 am
Ninja's_RGR'us (10/19/2011)
mbender (10/19/2011)
that stinks, but thanks for all you help very informative as usualIt's back online now. So hopefullly no more than 1-2 millions users got shut out ;-).
Maybe 200-300 at most. It's not a heavy-traffic blog.
I'm going to take it offline shortly until I've done some investigation as to the cause of the CPU spike (and from what suport told me, it was just a spike)
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
October 19, 2011 at 10:53 am
GilaMonster (10/19/2011)
Ninja's_RGR'us (10/19/2011)
mbender (10/19/2011)
that stinks, but thanks for all you help very informative as usualIt's back online now. So hopefullly no more than 1-2 millions users got shut out ;-).
Maybe 200-300 at most. It's not a heavy-traffic blog.
I'm going to take it offline shortly until I've done some investigation as to the cause of the CPU spike (and from what suport told me, it was just a spike)
Google bot maybe or another scrapper?
Not much in WP to cause a spike...
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply