CODEWITHSUNDEEP
sql-servert-sqldatabase-performance
mike G
mike G

Reputation: 27

Derived Tables vs Joins

Is it better to use derived tables to calculate your aggregates or use joins. In the example below the derived table is the entire query, but performance wise it faster? **I do not have query plan and cannot see the difference in spend % **

           SELECT
              sl_ytd.state,
              sl_ytd.num_stores,
              sl_ytd.ytd_sales
           FROM  
               (SELECT
                  SUM(sis.sales_dollars_ytd) as ytd_sales,
                  COUNT(DISTINCT s.store_key) as num_stores,
                  s.state
               FROM snapshot_item_store sis
               JOIN stores s on s.store_key = sis.store_key
               GROUP BY 
                  s.state) sl_ytd

Upvotes: 0

Views: 820

Answers (1)

Bruce Dunwiddie
Bruce Dunwiddie

Reputation: 2908

If you calculate the aggregate in the derived table by referencing only the child table, the "group by" can operate against the non clustered index on the foreign key. If you instead do a join and then calculate the aggregate against all return columns, it will have to generate a temp table and do a lot of extra work.

SELECT
    c.CompanyName,
    ISNULL(cu.UserCount, 0) AS UserCount
FROM
    Company c
        LEFT OUTER JOIN
        (
            SELECT
                u.CompanyID,
                COUNT(*) AS UserCount
            FROM
                [User] u
            GROUP BY
                u.CompanyID
        ) cu ON
            cu.CompanyID = c.CompanyID
ORDER BY
    c.CompanyName;

vs

SELECT
    c.CompanyName,
    ISNULL(COUNT(u.CompanyID), 0) AS UserCount
FROM
    Company c
        LEFT OUTER JOIN [User] u ON
            u.CompanyID = c.CompanyID
GROUP BY
    c.CompanyID
ORDER BY
    c.CompanyName;

Upvotes: 1

Related Questions