T-SQL Performance Question

Posted on 2005-05-12
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,, 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
    LVL 3

    Assisted Solution

    the second query is better

    Author Comment

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

    Accepted Solution

    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


    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    731 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now