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 «««23456»»

Sub query expressions vs. Joins. Expand / Collapse
Author
Message
Posted Friday, July 23, 2010 12:51 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 12:10 PM
Points: 1,414, Visits: 4,541
gary.strange-1058508 (7/22/2010)
Some of the comments posted seem to be following the lines of "what if this, what if that, this isn't quite right".

I would agree that the article may leave some discrepancies but I think it highlights a novice error that I've seen at every company I've worked for.

If you're querying tables that have a few thousand, maybe even tens of thousands of records, with ever lowering costs of high performing hardware, you probably don't need to care about how the query is constructed or executed. 50ms or 500ms means nothing to an end user and you'll get no reward for your troubles.

But if you are dealing with millions/billions of records this is definitely a lesson to be learnt. You'll achieve massive performance increase by helping the query optimizer and giving it a well structured query before it has to process it. Then just sit back and enjoy the praise from your boss.


we have tables like this and my advice is always to break your searches into smaller data sets. i've seen people query a few years of data at once and it literally takes days to run. if you were to break this into smaller queries you would have your data in hours.


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #958183
Posted Saturday, July 24, 2010 5:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 17, 2014 4:22 AM
Points: 43, Visits: 471
bit vague ???

got any examples of what you're talking about?
Post #958370
Posted Sunday, July 25, 2010 5:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 5:49 AM
Points: 12, Visits: 45
.. glad I always try to avoid the use of subqueries in my applications. This was done by intuition as I didn't know of the tabs in Management Studio. I hate the fact that I discovered this functionality so late.

I added an index to a table citizen(lastname asc, firstname asc) as this is the normal way I order selects for that table. Assumed it would make a difference but according to the execution plan the index wasn't even used. Is my assumption wrong or am I doing it wrong?
Post #958476
Posted Sunday, July 25, 2010 1:08 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 35,366, Visits: 31,905
hrc_public (7/25/2010)
.. glad I always try to avoid the use of subqueries in my applications.


Ummmm.... why? We just proved that the article is incorrect and that certain correctly formed subqueries can actually be faster than a classic inner join. Read the rest of the discussion above.


--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 #958512
Posted Sunday, July 25, 2010 11:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 8, 2011 1:04 PM
Points: 5, Visits: 27
This seems to be a correlated sub-query. Does this apply to non correlated sub-query?
Post #958566
Posted Monday, July 26, 2010 3:57 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:04 AM
Points: 4,417, Visits: 10,716
Jeff Moden (7/22/2010)
Maybe that's true in Oracle but it's not true in SQL Server. Oracle is mostly rule based and SQL Server is cost based.


I'm glad you commented this article, Jeff. Great discussion.
A minor note on the Oracle thing: it used to be completely rule-based, but now it's entirely cost-based. Starting from version 10g the rule-based optimizer is no longer available.


--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #958671
Posted Monday, July 26, 2010 6:32 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 35,366, Visits: 31,905
cmapowers (7/25/2010)
This seems to be a correlated sub-query. Does this apply to non correlated sub-query?


All non correlated sub-queries break down to being an inline view whether they're in the form of a derived table in the FROM clause or a CTE reference in the FROM clause) and are typically joined as if a table. So the link to Gail's blog still applies.


--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 #958746
Posted Monday, July 26, 2010 6:34 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 35,366, Visits: 31,905
Gianluca Sartori (7/26/2010)
A minor note on the Oracle thing: it used to be completely rule-based, but now it's entirely cost-based. Starting from version 10g the rule-based optimizer is no longer available.


Thanks for the update on Oracle, Gianluca. It HAS been a while since I've had to work with it at such a level.


--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 #958747
Posted Monday, July 26, 2010 6:37 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:04 AM
Points: 4,417, Visits: 10,716
Jeff Moden (7/26/2010)
Gianluca Sartori (7/26/2010)
A minor note on the Oracle thing: it used to be completely rule-based, but now it's entirely cost-based. Starting from version 10g the rule-based optimizer is no longer available.


Thanks for the update on Oracle, Gianluca. It HAS been a while since I've had to work with it at such a level.


You don't know how lucky you are...


--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #958750
Posted Monday, July 26, 2010 6:42 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 35,366, Visits: 31,905
Gianluca Sartori (7/26/2010)
Jeff Moden (7/26/2010)
Gianluca Sartori (7/26/2010)
A minor note on the Oracle thing: it used to be completely rule-based, but now it's entirely cost-based. Starting from version 10g the rule-based optimizer is no longer available.


Thanks for the update on Oracle, Gianluca. It HAS been a while since I've had to work with it at such a level.


You don't know how lucky you are...


Heh... actually, I do. Better yet, one of the things I get to do at my "new" job is migrate some Oracle databases and related code to SQL Server.


--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 #958757
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse