SQL CASE vs JOIN efficiency

Question

In my MS SQL Server database I am pulling transaction data based on a variety of different codes that are in one column.

Would it be more efficient to:

• join the same table over and over for each code in a WHERE clause

• do multiple case statements on the whole table (shown below)

• do multiple case statements on the whole table but limit it by a WHERE SubsidCde IN ('AA','BA','BB', etc) clause

We have so many queries running per second that even though I have tried all 3 methods I get no definitive results.

SELECT
    SUM(CASE WHEN Subsid_Cde = 'AA' THEN Trans_Amt END),0) [AA],
    SUM(CASE WHEN Subsid_Cde = 'BA' THEN Trans_Amt END),0) [BA],
    SUM(CASE WHEN Subsid_Cde = 'BB' THEN Trans_Amt END),0) [BB]
FROM
    Transactions

--  There are 8 more rows like this, using a different code for each line

Answer 1

Use this:

SELECT  (
        SELECT  SUM(Trans_Amt)
        FROM    Transactions
        WHERE   Subsid_Cde = 'AA'
        ) AS sum_aa,
        (
        SELECT  SUM(Trans_Amt)
        FROM    Transactions
        WHERE   Subsid_Cde = 'BB'
        ) AS sum_bb

, without external FROM or WHERE clause.

In SQL Server 2005+, use this:

SELECT  [AA], [BB]
FROM    (
        SELECT  trans_amt, subsid_cde
        FROM    transactions
        ) q
PIVOT   (
        SUM(trans_amt)
        FOR subsid_cde IN (['AA'], ['BB'])
        )

Answer 2

Try using a temporary table.

Answer 3

If you're summing all possible (or most) values of Subsid_Cde field, then CASE is faster as it won't scan the table multiple times as it aggregates the sums. If you only looking for a small subset of possible Subsid_Cde fields then separate selects / joins (along with an index on Subsid_Cde) will work faster.

You need to learn to read Execution Plans, then you'll be able to figure such things by yourself.

Also, alternatively, you could do a GROUP BY on Subsid_Cde wrapped into a PIVOT clause (google for PIVOT MS SQL SERVER 2005)

Answer 4

3 is your best bet. It's easy to read, it's easy to modify later on, and it should use the indexes that you've defined and expect to be using (still, check).

--1 Sometimes you have to join to the same table. But this isn't one of them and joining every time you need to include a new Subsid_Cde makes for less readable SQL without really gaining anything.

--2 Transaction tables tend to grow very large, so you NEVER want to scan the entire table. So #2 is definitely out, unless the codes you'll be using in your query gives you back all of the rows anyway.