Query with many CASE statements - optimization

Question

I have one very dirty query that per sure can be optimized because there are so many CASE statements in it!

SELECT 
    (CASE pa.KplusTable_Id WHEN 1 THEN sp.sp_id 
          WHEN 2 THEN fw.fw_id
          WHEN 3 THEN s.sw_Id
          WHEN 4 THEN id.ia_id END) as Deal_Id,
max(CASE pa.KplusTable_Id WHEN 1 THEN sp.Trans_Id 
                          WHEN 2 THEN fw.Trans_Id
                          WHEN 3 THEN s.Trans_Id
                          WHEN 4 THEN id.Trans_Id END) as TransId_CurrentMax
INTO #MaxRazlicitOdNull
FROM #PotencijalniAktuelni pa LEFT JOIN kplus_sp sp (nolock) on sp.sp_id=pa.Deal_Id AND pa.KplusTable_Id=1
    LEFT JOIN kplus_fw fw (nolock) on fw.fw_id=pa.Deal_Id AND pa.KplusTable_Id=2        
    LEFT JOIN dev_sw s (nolock) on s.sw_Id=pa.Deal_Id AND pa.KplusTable_Id=3
    LEFT JOIN kplus_ia id (nolock) on id.ia_id=pa.Deal_Id AND pa.KplusTable_Id=4
WHERE isnull(CASE pa.KplusTable_Id WHEN 1 THEN sp.BROJ_TIKETA 
                                   WHEN 2 THEN fw.BROJ_TIKETA
                                   WHEN 3 THEN s.tiket
                                   WHEN 4 THEN id.BROJ_TIKETA END, '')<>'' 
GROUP BY CASE pa.KplusTable_Id WHEN 1 THEN sp.sp_id 
                               WHEN 2 THEN fw.fw_id
                               WHEN 3 THEN s.sw_Id
                               WHEN 4 THEN id.ia_id END

Because I have same condition couple times, do you have idea how to optimize query, make it simpler and better. All suggestions are welcome!

TnX in advance!

Nemanja

Solution 1

To me this looks like a botched attempt in sub-typing. This is what I think you have now.

Based on the model, the following should work:

;
with
q_00 as (
    select
         pa.Deal_Id                                                             as Deal_Id
       , coalesce(sp.BROJ_TIKETA, fw.BROJ_TIKETA, sw.tiket, ia.BROJ_TIKETA, '') as Ticket_No
       , coalesce(sp.Trans_Id, fw.Trans_Id, sw.Trans_Id, ia.Trans_Id)           as Trans_Id
    from #PotencijalniAktuelni as pa
    left join kplus_sp         as sp on sp.sp_Id = pa.Deal_Id and pa.KplusTable_Id = 1
    left join kplus_fw         as fw on fw.fw_Id = pa.Deal_Id and pa.KplusTable_Id = 2        
    left join dev_sw           as sw on sw.sw_Id = pa.Deal_Id and pa.KplusTable_Id = 3
    left join kplus_ia         as ia on ia.ia_Id = pa.Deal_Id and pa.KplusTable_Id = 4
)
select
      Deal_Id
    , max(Trans_Id) as TransId_CurrentMax
into #MaxRazlicitOdNull
from  q_00
where Ticket_No <> ''
group by Deal_Id ;

SQL Server 2005 +

Solution 2

The quickest query may be to union each of the 4 clauses out and union them together. The code ends up longer but its much more clear what each block does - especially if you comment them together.

-- When KplusTable_Id  = 1
Select 
 sp.sp_id as as Deal_Id,
max(sp.Trans_Id) as TransId_CurrentMax
FROM #PotencijalniAktuelni pa LEFT JOIN kplus_sp sp (nolock) on sp.sp_id=pa.Deal_Id AND pa.KplusTable_Id=1
    LEFT JOIN kplus_fw fw (nolock) on fw.fw_id=pa.Deal_Id AND pa.KplusTable_Id=2        
    LEFT JOIN dev_sw s (nolock) on s.sw_Id=pa.Deal_Id AND pa.KplusTable_Id=3
    LEFT JOIN kplus_ia id (nolock) on id.ia_id=pa.Deal_Id AND pa.KplusTable_Id=4
WHERE sp.BROJ_TIKETA <>'' 
and pa.KplusTable_Id = 1
GROUP BY  sp.sp_id 

Union ...

-- When 2

Wrap the entire query in a select to do your insert into #MaxRazlicitOdNull

Solution 3

The Cases is Ok for me. Usually are faster than Union. Put several variants of your query and compare batch in Plan. Only one (insignificant) details Change

WHERE isnull(CASE pa.KplusTable_Id WHEN 1 THEN sp.BROJ_TIKETA 
                                   WHEN 2 THEN fw.BROJ_TIKETA
                                   WHEN 3 THEN s.tiket
                                   WHEN 4 THEN id.BROJ_TIKETA END, '')<>'' 

for this

WHERE CASE pa.KplusTable_Id WHEN 1 THEN sp.BROJ_TIKETA 
                                   WHEN 2 THEN fw.BROJ_TIKETA
                                   WHEN 3 THEN s.tiket
                                   WHEN 4 THEN id.BROJ_TIKETA END is not null

Another sol (with UNION):

SELECT  pa.Deal_Id, MAX(Q.Trans_Id) AS TransId_CurrentMax
INTO #MaxRazlicitOdNull
FROM 
(SELECT 1 A KplusTable_Id, Trans_Id, sp_id AS Deal_Id  FROM kplus_sp
UNION 
SELECT 2 AS KplusTable_Id, Trans_Id, fw_id AS Deal_Id FROM  kplus_fw  
UNION 
SELECT 3 AS KplusTable_Id, Trans_Id, sw_Id AS Deal_Id FROM  dev_sw
UNION 
SELECT 4 AS KplusTable_Id, Trans_Id, ia_id AS Deal_Id FROM kplus_ia) AS Q 
 INNER  JOIN #PotencijalniAktuelni pa ON pa.KplusTable_Id=Q.KplusTable_Id AND pa.Deal_Id=Q.Deal_Id
 GROUP BY pa.Deal_Id

Test every variant of query in plan and choose faster

Comments

[marc_s]

Wouldn't it be easier to just have four simple SELECT statement, one for each case of KplusTable_Id, and then UNION them together?? That way, you could forget about all the CASE stuff....

[marc_s]

Actually, you could probably also reduce the number of LEFT JOIN's to just the one that "fits" your current criteria (KPlusTable_Id = 1 --> LEFT JOIN kplus_sp - all the others are not relevant here)

[Nemanja Vujacic]

@Doc Brown - All kplus tables (including dev_sw) have similar structure (probably will not useful to provide all details about tables, so I will not waste your time with that), data are storing (choosing appropriate table) depending on deal type. Temp table #PotencijalniAktuelni is storing data that are filtered previously and consist Deal_Id as well as number of table (that will be used in CASE statement), as well as couple other columns that are not important in this example. Please let me know what other information could be useful and I will try to provide it.

[Nemanja Vujacic]

I have seen that practice in my company is using isnull(something, '')<>''. At the beginning that was a little bit weird syntax for me, but later I accepted it. Do you know is there some important difference between that at 'is not null' statement?

[Nemanja Vujacic]

As renegm said (and I had some readings in mean time), UNION can be very 'expensive' operation (especially when have tables with a lot of data), so I believe that this solution with LEFT JOIN-s and COALESCE is very good. Thank you Damir!

[renegm]

Important differences? Depends on how you look. One difference is: comparison vs evaluacón+comparison+comparison. The SQLServer optimize this usually but not always. And in cases like this, when you need to know if row is null, I prefer the null value to look for substitutes. Substitutes can generate a type error or use an existing value. And about UNION. Union will almost always be slower because scans the same table more than once. Union look like a painter's algorithm. But it is easy to check: Put both variants together and asks the plan. Union consistently over 50% costs

[Nemanja Vujacic]

Today I compared these two solutions (UNION vs LEFT JOIN-s) and didn't found any big difference in execution time. Reason for that could be that part of stored procedure that includes this code is only 2% of time in Execution plan, so because of that, difference cannot be obvious.