时间戳

首页 > 解决方案 > 使用联合优化查询

sql - 使用联合优化查询

问题描述

我必须优化以下查询，执行时间太长

SELECT Devis.Numero_Devis, 
       contrat.Numero_contrat, 
       Devis.Id_Devis, 
       contrat.ID_contrat 
FROM   (SELECT ID_contrat          AS Id_Devis, 
               Numero_contrat AS Numero_Devis 
        FROM   [sch_DM_LMI].Fact_IU_contrat AS contrat 
        WHERE  ( Code_Statut_Contrat = 'D' )) AS Devis 
       LEFT OUTER JOIN [sch_DM_LMI].Fact_IU_contrat AS contrat 
                    ON Devis.Numero_Devis = contrat.Numero_contrat 
                       contratD contrat.Code_Statut_Contrat = '1' 
UNION 
SELECT 'Inconnu'                              AS Numero_Devis, 
       'Inconnu'                              AS Numero_contrat, 
       '00000000-0000-0000-0000-000000000000' AS Id_Devis, 
       '00000000-0000-0000-0000-000000000000' AS ID_contrat

我创建了以下索引

CREATE NONCLUSTERED INDEX idx_Devis
ON [sch_DM_LMI].[Fact_IU_contrat] ([Code_Statut_Contrat])
INCLUDE ([ID_contrat],[Numero_contrat])

这里是执行计划：

在此处输入图像描述 如何优化它？

标签： sqltsqlsql-server-2016

解决方案

我建议这样编写查询：

SELECT 'Inconnu'                              AS Numero_Devis, 
       'Inconnu'                              AS Numero_contrat, 
       '00000000-0000-0000-0000-000000000000' AS Id_Devis, 
       '00000000-0000-0000-0000-000000000000' AS ID_contrat
UNION ALL  -- NOT UNION    
SELECT Devis.Numero_contrat, 
       contrat.Numero_contrat, 
       Devis.ID_contrat, 
       contrat.ID_contrat 
FROM [sch_DM_LMI].Fact_IU_contrat devis LEFT JOIN
     [sch_DM_LMI].Fact_IU_contrat contrat 
     ON contrat.Numero_contrat = Devis.Numero_contrat AND
        contrat.Code_Statut_Contrat = '1' 
WHERE devis.Code_Statut_Contrat = 'D';

这里唯一重要的变化是对UNION ALL.

然后对于这个查询，你需要索引Fact_IU_contrat(Code_Statut_Contrat, Numero_contrat)和Fact_IU_contrat(Numero_contrat, Code_Statut_Contrat)-- 是的，两者都有。您还可以包含id_contrat在两个索引中。

推荐阅读