SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sub query expressions vs. Joins.


Sub query expressions vs. Joins.

Author
Message
alen teplitsky
alen teplitsky
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7298 Visits: 4674
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.:-P


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.
gary.strange-sqlconsumer
gary.strange-sqlconsumer
SSC-Addicted
SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)SSC-Addicted (402 reputation)

Group: General Forum Members
Points: 402 Visits: 627
bit vague ???

got any examples of what you're talking about?
hrc
hrc
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 52
.. 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?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220148 Visits: 42002
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
cmapowers
cmapowers
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 27
This seems to be a correlated sub-query. Does this apply to non correlated sub-query?
Gianluca Sartori
Gianluca Sartori
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24621 Visits: 13362
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220148 Visits: 42002
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220148 Visits: 42002
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Gianluca Sartori
Gianluca Sartori
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24621 Visits: 13362
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.


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

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220148 Visits: 42002
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.


Crying 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. :-D

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search