使用union优化查询

8wtpewkr 于 2021-08-13 发布在 Java

关注(0)|答案(1)|浏览(354)

我必须优化下面的查询，执行起来太费时了

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

执行计划如下：

如何优化？

sql tsql sql-server-2016

来源：https://stackoverflow.com/questions/62062178/optimize-query-with-union

1条答案

按热度按时间

zsbz8rwp1#

我建议这样写查询：

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 在两个索引中。

赞(0）回复(0）举报 2021-08-13

我来回答

使用union优化查询

1条答案

相关问题

热门标签

最新问答