Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Which is Better? Expand / Collapse
Author
Message
Posted Monday, April 14, 2014 8:56 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, November 7, 2014 2:00 PM
Points: 979, Visits: 3,364
Hello Everyone
I am reviewing code in a SQL database that was coded by a BA and a Old oracle developer. Never a good idea to begin with. I am finding a lot of queries that should be using a JOIN, or LEFT OUTER JOIN, and they are using sub-queries with NOT IN. Plus s lot of just poorly written code.

I am looking for some solid proof as to which type of query is better. I have always known that using JOIN is always better for performance since one can index that column(s) that the JOIN will use. Which is usually a Primary key in the tables involved.

Can someone shed some light on this.

Thank you in advance for all your advice, suggestions and time.

Andrew SQLDBA
Post #1561507
Posted Monday, April 14, 2014 9:00 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 7:09 PM
Points: 593, Visits: 931
Aaron Bertrand has a great article on this topic http://www.sqlperformance.com/2012/12/t-sql-queries/left-anti-semi-join



Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1561511
Posted Monday, April 14, 2014 9:02 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:04 AM
Points: 35,546, Visits: 32,140
AndrewSQLDBA (4/14/2014)
Hello Everyone
I am reviewing code in a SQL database that was coded by a BA and a Old oracle developer. Never a good idea to begin with. I am finding a lot of queries that should be using a JOIN, or LEFT OUTER JOIN, and they are using sub-queries with NOT IN. Plus s lot of just poorly written code.

I am looking for some solid proof as to which type of query is better. I have always known that using JOIN is always better for performance since one can index that column(s) that the JOIN will use. Which is usually a Primary key in the tables involved.

Can someone shed some light on this.

Thank you in advance for all your advice, suggestions and time.

Andrew SQLDBA


To be honest, IN and NOT IN are actually a little bit faster than the related joins. There are some caveates for both that people need to understand. NOT IN can actually be a fair bit faster than an OUTER JOIN with a predicate that looks for NULLs.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1561513
Posted Monday, April 14, 2014 10:21 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, November 7, 2014 2:00 PM
Points: 979, Visits: 3,364
Thank you to you both.

I read the article, that is a very good one.

Thanks again
Andrew SQLDBA
Post #1561559
Posted Monday, April 14, 2014 10:49 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:04 AM
Points: 35,546, Visits: 32,140
Here's another excellent link on the subject. The article also has other links. Gail Shaw is a Microsoft Certified Master and she has put together quite the writeup on these subjects including some of the caveates of using one vs another.

http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1561574
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse