T-SQL Performance Question

Posted on 2005-05-12
Medium Priority
Last Modified: 2010-03-19
Which genarally performs better?

Select id, (select something from table), (select something from table1)
from anotherTable inner join anothertable 2 .......


select 1,id, table1.id, table2,id
from 1
inner join (select id from table) table1 on 1 = table1
inner join (select ID from table2) anothertable on 1 = anothertable
Question by:vinny45

Assisted Solution

poaysee earned 400 total points
ID: 13988250
the second query is better

Author Comment

ID: 13988474
cool thats good to know, but is there a reason why?
LVL 32

Accepted Solution

Brendt Hess earned 800 total points
ID: 13988482
The second query is definitely better.  Why?

In the first case, the subselects are executed for each row generated.  In the second, the JOINs are performed inside SQL Server as masses of rows (especially on indexed fields).  This means that, in the case of a 100 row resultset, the first query would generate 1 + (100) + (100) = 201 SQL Statements executed, while the second would generate 1 SQL Statement (with more steps, but still only one statement).  Just getting rid of the overhead of starting each SQL Statement will improve your query speed and decrease your resource usage.
LVL 28

Assisted Solution

rafrancisco earned 800 total points
ID: 13988787

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question