查询优化

Question

下面的查询运行了7个小时。有人能帮我优化查询吗？

select    count(1) 
FROM      temp_raw_appointments trp
left join temp_raw_transactions_sep tra
on        case  when tra.[rndrng prvdr] in (SELECT [rndrng prvdr] 
                                            FROM   temp_raw_changetosupprov)
                     then tra.[sup prvdr] 
                when tra.[rndrng prvdr] = 'P003_BMI_WidermannJ' 
                     then 'P003_WiedermannJ' -- Kim Chirayil's customization, 06/16
                when tra.[rndrng prvdr] = 'P014_HeerenK' 
                     then 'P014_HarrisonL' -- Kim Chirayil's customization, 06/16
                else tra.[rndrng prvdr] end 
          =trp.[appt schdlng prvdr] 
where     tra.[rndrng prvdr] is null

Answer 1

尝试将查询拆分为两部分

CREATE TABLE #inter
  (
     temp_col VARCHAR(500)
  )

INSERT INTO #inter
SELECT Distinct CASE
         WHEN tra.[rndrng prvdr] IN (SELECT [rndrng prvdr]
                                     FROM   temp_raw_changetosupprov) THEN tra.[sup prvdr]
         WHEN tra.[rndrng prvdr] = 'P003_BMI_WidermannJ' THEN 'P003_WiedermannJ' -- Kim Chirayil's customization, 06/16
         WHEN tra.[rndrng prvdr] = 'P014_HeerenK' THEN 'P014_HarrisonL' -- Kim Chirayil's customization, 06/16
         ELSE tra.[rndrng prvdr]
       END AS temp_col
FROM   temp_raw_transactions_sep tra

CREATE NONCLUSTERED INDEX nix_inter
  ON #inter (temp_col)

SELECT Count(1)
FROM   temp_raw_appointments trp
WHERE  NOT EXISTS (SELECT 1
                   FROM   #inter
                   WHERE  trp.[appt schdlng prvdr] = temp_col) 

由于它已经运行了7个小时，所以我并不期待执行计划。如果你有一个，请提供进一步的建议

Answer 2

试试这个:更新了连接条件的逻辑。这可能有助于性能调优。

select count(1) 
FROM temp_raw_appointments trp
left join temp_raw_transactions_sep tra on (tra.[sup prvdr] = trp.[appt schdlng prvdr]  AND EXIST (SELECT 1 FROM temp_raw_changetosupprov [rndrng prvdr] =tra.[rndrng prvdr])
OR ( trp.[appt schdlng prvdr] = tra.[rndrng prvdr] AND tra.[rndrng prvdr] IN ('P003_BMI_WidermannJ','P014_HeerenK'))
OR (tra.[rndrng prvdr] = trp.[appt schdlng prvdr] )   
where tra.[rndrng prvdr] is null

Answer 3

当然，看到执行计划、执行统计数据和行数将是很好的，但是，请考虑以下经过深思熟虑的猜测作为一种可能的解决方案：

1)确保tr.prvdr、temp_raw_changetosupprov.rndrng prvdr、trp.appt schdlng prvdr列已被索引。2)重写查询，提高查询效率。由于您只对LEFT JOIN之后的IS NULL行感兴趣，因此我猜以下查询的逻辑将是正确的：

WITH SupPrvdr([sup prvdr])
AS
(
    SELECT tra.[sup prvdr] FROM temp_raw_transactions_sep tra
    WHERE tra.[rndrng prvdr] IN
    (
        SELECT [rndrng prvdr] FROM temp_raw_changetosupprov
        UNION 
        SELECT 'P003_WiedermannJ' AS [rndrng prvdr] 
        UNION 
        SELECT 'P014_HarrisonL'AS [rndrng prvdr] 
    ) 
)
SELECT COUNT(*) FROM temp_raw_appointments trp
WHERE trp.[appt schdlng prvdr] NOT IN (SELECT [sup prvdr] FROM SupPrvdr)
    OR trp.[appt schdlng prvdr] IS NULL -- if trp.[appt schdlng prvdr] is nullable column this condition can be omitted