Query with many CASE operators - optimization

Question

I have one very dirty query that can certainly 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

Since I have the same condition a couple of times, you have an idea how to optimize the request, make it simpler and better. All suggestions are welcome!

Tnx in advance!

Nemanha

Answer 1

For me, this is like a failed subtype attempt. This is what I think you have.

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 +

Answer 2

. , , , .

-- 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

select, #MaxRazlicitOdNull

Answer 3

Things are all right for me. Usually they are faster than the Union. Put a few variations of your query and compare the package in Plan. Only one (insignificant) information 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

Other 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

Check each query option in the plan and choose faster