具有多个 CASE 语句的查询 - 优化

Question

我有一个非常脏的查询，它肯定可以优化，因为其中有很多 CASE 语句！

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

因为我有几次相同的条件，你知道如何优化查询，使其更简单、更好吗？欢迎所有建议！

提前TnX！

内马尼亚

Answer 1

对我来说，这看起来像是一次拙劣的子类型尝试。这就是我认为你现在所拥有的。

根据模型，以下内容应该有效：

;
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

最快的查询可能是将 4 个子句中的每一个子句联合起来并将它们联合在一起。代码最终更长，但每个块的作用更加清晰 - 特别是如果您将它们一起注释的话。

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

将整个查询包装在选择中以将插入插入#MaxRazlicitOdNull

Answer 3

这些案件对我来说还可以。通常比 Union 更快。
将查询的多个变体放入计划中并比较批次。
只有一个（无关紧要的）细节
进行更改

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, '')<>'' 

对此

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

另一个 sol（使用 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

测试计划中查询的每个变体并选择更快