如何在存储过程中找到使SQL查询更高效、更快的最佳方法

Question

有人能给我一些指导或建议来改进下面的查询吗？运行包含以下查询的作业几乎需要2天时间。瓶颈是选择查询选择A.codeA + TrimCode，A.RevisionDate.大查询在存储过程的中间执行，下面的大查询是导致问题的查询。这项工作只有在两天后才能完成。返回的行结果号为200万行。我已经检查了下面两个表中的行数，并找出了执行以下查询需要花费很长时间的原因：--从CADEAUX.dbo.EI03_VehExtInfo中选择COUNT()；--返回1170454行--从CADEAUX.dbo.EI04_VehExtInfoDetail中选择COUNT()；--返回10405145行

create table #codesTable (
        codeA           char(11),
        TrimCode        char(2),
        RevisionDate    datetime,
        LngCode         char(2),
        );

    create clustered index indx1 on #codesTable (codeA, TrimCode, LngCode)
    create index indx2 on #codesTable (codeA, TrimCode, RevisionDate, LngCode)

    insert into #codesTable (codeA, TrimCode, RevisionDate, LngCode)
    select  substring(codeA,1,11)
            , substring(codeA,12,2)
            , RevisionDate
            , LngCode
    from    table_II_ModelOpts.dbo.table05_TRIM
    union
    select  substring(codeA,1,11)
            , substring(codeA,12,2)
            , RevisionDate
            , LngCode
    from    table_II_Translate.dbo.table05_TRIM
    union
    select  substring(codeA,1,11)
            , substring(codeA,12,2)
            , RevisionDate
            , LngCode
    from    table_II_Translate_CHYLR.dbo.table05_TRIM
    union
    select  substring(codeA,1,11)
            , substring(codeA,12,2)
            , RevisionDate
            , LngCode
    from    table_II_Translate_Fleet.dbo.table05_TRIM
    union
    select  substring(codeA,1,11)
            , substring(codeA,12,2)
            , RevisionDate
            , LngCode
    from    table_II_Translate_FCA.dbo.table05_TRIM
    union
    select  substring(codeA,1,11)
            , substring(codeA,12,2)
            , RevisionDate
            , LngCode
    from    table_II_Translate_GCA.dbo.table05_TRIM
    union
    select  substring(codeA,1,11)
            , substring(codeA,12,2)
            , RevisionDate
            , LngCode
    from    table_II_Translate_Pre92.dbo.table05_TRIM


    create table #VT51codeAs (
        codeA           char(13),
        LngCode         char(2)
        );

    create clustered index indx1 on #VT51codeAs (codeA, LngCode)

    insert into #VT51codeAs (codeA, LngCode)
    select  codeA
            , LngCode
    from    table_II_ModelOpts.dbo.VT51_codeAs
    union
    select  codeA
            , LngCode
    from    table_II_Translate.dbo.VT51_codeAs
    union
    select  codeA
            , LngCode
    from    table_II_Translate_CHYLR.dbo.VT51_codeAs
    union
    select  codeA
            , LngCode
    from    table_II_Translate_Fleet.dbo.VT51_codeAs
    union
    select  codeA
            , LngCode
    from    table_II_Translate_FCA.dbo.VT51_codeAs
    union
    select  codeA
            , LngCode
    from    table_II_Translate_GCA.dbo.VT51_codeAs
    union
    select  codeA
            , LngCode
    from    table_II_Translate_Pre92.dbo.VT51_codeAs

   insert into #EI02 (
          codeA 
        , RevisionDate
        , LngCode
        , ModifiedOn
        , InfoCode
        , ExtInfoVariation 
        , Info1
        , Info2
        , Info3
        , Info4
        , Info5
        , Info6
        , Info7
        , Info8
        , Info9
        , Info10
        , Info11
        , Info12
        , Info13
        , Info14
        , Info15
        , Logic
    )
    SELECT 
          A.codeA + TrimCode
        , A.RevisionDate
        , A.LngCode
        , ISNULL(EI.ModifiedOn,GetDate())
        , EI03.ExtInfoCode
        , EI03.ExtInfoVariation 
        , Column1 = isnull((SELECT ExtInfoData FROM [LXXD-TRXI-PLO1].CADEAUX.dbo.EI04_VehExtInfoDetail  WHERE ExtInfoUID = 1 AND codeA = EI03.codeA AND ExtInfoVariation = EI03.ExtInfoVariation AND ExtInfoCode = EI03.ExtInfoCode AND RevCode = EI03.RevCode),'')
        , Column2 = isnull((SELECT ExtInfoData FROM [LXXD-TRXI-PLO1].CADEAUX.dbo.EI04_VehExtInfoDetail  WHERE ExtInfoUID = 2 AND codeA = EI03.codeA AND ExtInfoVariation = EI03.ExtInfoVariation AND ExtInfoCode = EI03.ExtInfoCode AND RevCode = EI03.RevCode),'')
        , Column3 = isnull((SELECT ExtInfoData FROM [LXXD-TRXI-PLO1].CADEAUX.dbo.EI04_VehExtInfoDetail  WHERE ExtInfoUID = 3 AND codeA = EI03.codeA AND ExtInfoVariation = EI03.ExtInfoVariation AND ExtInfoCode = EI03.ExtInfoCode AND RevCode = EI03.RevCode),'')
        , Column4 = isnull((SELECT ExtInfoData FROM [LXXD-TRXI-PLO1].CADEAUX.dbo.EI04_VehExtInfoDetail  WHERE ExtInfoUID = 4 AND codeA = EI03.codeA AND ExtInfoVariation = EI03.ExtInfoVariation AND ExtInfoCode = EI03.ExtInfoCode AND RevCode = EI03.RevCode),'')
        , Column5 = isnull((SELECT ExtInfoData FROM [LXXD-TRXI-PLO1].CADEAUX.dbo.EI04_VehExtInfoDetail  WHERE ExtInfoUID = 5 AND codeA = EI03.codeA AND ExtInfoVariation = EI03.ExtInfoVariation AND ExtInfoCode = EI03.ExtInfoCode AND RevCode = EI03.RevCode),'')
        , Column6 = isnull((SELECT ExtInfoData FROM [LXXD-TRXI-PLO1].CADEAUX.dbo.EI04_VehExtInfoDetail  WHERE ExtInfoUID = 6 AND codeA = EI03.codeA AND ExtInfoVariation = EI03.ExtInfoVariation AND ExtInfoCode = EI03.ExtInfoCode AND RevCode = EI03.RevCode),'')
        , Column7 = isnull((SELECT ExtInfoData FROM [LXXD-TRXI-PLO1].CADEAUX.dbo.EI04_VehExtInfoDetail  WHERE ExtInfoUID = 7 AND codeA = EI03.codeA AND ExtInfoVariation = EI03.ExtInfoVariation AND ExtInfoCode = EI03.ExtInfoCode AND RevCode = EI03.RevCode),'')
        , Column8 = substring(isnull((SELECT ExtInfoData FROM [LXXD-TRXI-PLO1].CADEAUX.dbo.EI04_VehExtInfoDetail  WHERE ExtInfoUID = 8 AND codeA = EI03.codeA AND ExtInfoVariation = EI03.ExtInfoVariation AND ExtInfoCode = EI03.ExtInfoCode AND RevCode = EI03.RevCode),''),1,250)
        , Column9 = isnull((SELECT ExtInfoData FROM [LXXD-TRXI-PLO1].CADEAUX.dbo.EI04_VehExtInfoDetail  WHERE ExtInfoUID = 9 AND codeA = EI03.codeA AND ExtInfoVariation = EI03.ExtInfoVariation AND ExtInfoCode = EI03.ExtInfoCode AND RevCode = EI03.RevCode),'')
        , Column10 = isnull((SELECT ExtInfoData FROM [LXXD-TRXI-PLO1].CADEAUX.dbo.EI04_VehExtInfoDetail  WHERE ExtInfoUID = 10 AND codeA = EI03.codeA AND ExtInfoVariation = EI03.ExtInfoVariation AND ExtInfoCode = EI03.ExtInfoCode AND RevCode = EI03.RevCode),'')
        , Column11 = isnull((SELECT ExtInfoData FROM [LXXD-TRXI-PLO1].CADEAUX.dbo.EI04_VehExtInfoDetail  WHERE ExtInfoUID = 11 AND codeA = EI03.codeA AND ExtInfoVariation = EI03.ExtInfoVariation AND ExtInfoCode = EI03.ExtInfoCode AND RevCode = EI03.RevCode),'')
        , Column12 = isnull((SELECT ExtInfoData FROM [LXXD-TRXI-PLO1].CADEAUX.dbo.EI04_VehExtInfoDetail  WHERE ExtInfoUID = 12 AND codeA = EI03.codeA AND ExtInfoVariation = EI03.ExtInfoVariation AND ExtInfoCode = EI03.ExtInfoCode AND RevCode = EI03.RevCode),'')
        , Column13 = isnull((SELECT ExtInfoData FROM [LXXD-TRXI-PLO1].CADEAUX.dbo.EI04_VehExtInfoDetail  WHERE ExtInfoUID = 13 AND codeA = EI03.codeA AND ExtInfoVariation = EI03.ExtInfoVariation AND ExtInfoCode = EI03.ExtInfoCode AND RevCode = EI03.RevCode),'')
        , Column14 = isnull((SELECT ExtInfoData FROM [LXXD-TRXI-PLO1].CADEAUX.dbo.EI04_VehExtInfoDetail  WHERE ExtInfoUID = 14 AND codeA = EI03.codeA AND ExtInfoVariation = EI03.ExtInfoVariation AND ExtInfoCode = EI03.ExtInfoCode AND RevCode = EI03.RevCode),'')
        , Column15 = isnull((SELECT ExtInfoData FROM [LXXD-TRXI-PLO1].CADEAUX.dbo.EI04_VehExtInfoDetail  WHERE ExtInfoUID = 15 AND codeA = EI03.codeA AND ExtInfoVariation = EI03.ExtInfoVariation AND ExtInfoCode = EI03.ExtInfoCode AND RevCode = EI03.RevCode),'')
        , EI03.Logic
    FROM [LXXD-TRXI-PLO1].CADEAUX.dbo.EI03_VehExtInfo EI03 
          inner join [LXXD-TRXI-PLO1].CADEAUX.dbo.EI04_VehExtInfoDetail EI04 on EI03.codeA = EI04.codeA and ei04.ExtInfoCode = EI03.ExtInfoCode
          and ei04.ExtInfoVariation = ei03.ExtInfoVariation and ei04.RevCode = ei03.RevCode  
          and ei04.ExtInfoUID = 1

        inner join #codesTable A on (
                EI03.codeA = A.codeA 
            AND Deleted = 'F' 
            AND dbo.TrimMatches(A.TrimCode,OptionTrims) = 1
            )
        left join EI_Modified E on (E.codeA = substring(A.codeA,1,11) and E.LngCode = A.LngCode)
        left join ( 
            select  codeA, MAX(ModifiedOn) ModifiedOn from [LXXD-TRXI-PLO1].CADEAUX.dbo.VT12_VehicleModified where  SectionUID in ( 44,45,47,48,49) group by codeA 
            ) EI
            on (EI.codeA = substring(EI03.codeA,1,11))       
    where 
        (
            isnull(E.ModifiedOn,-1) < isnull(EI.ModifiedOn,-1) 
            or 
            A.codeA+A.TrimCode not in (select codeA from EI02_ExtVehInfo EI02 where A.RevisionDate = EI02.RevisionDate and A.LngCode = EI02.LngCode and EI02.InfoCode = EI03.ExtInfoCode)
            OR
            (   ((E.ModifiedOn = EI.ModifiedOn) AND E.ModifiedOn IS NOt NULL )
                AND 
                ((SELECT Top 1 Info1 FROM EI02_ExtVehInfo EI2 
                WHERE EI2.RevisionDate = A.RevisionDate and EI2.LngCode = A.LngCode and EI2.InfoCode = EI03.ExtInfoCode AND EI2.codeA = A.codeA+A.TrimCode) = '')
            )
            OR
            (   ((E.ModifiedOn = EI.ModifiedOn) AND E.ModifiedOn IS NOt NULL )
                AND 
                ((SELECT Top 1 Info1 FROM EI02_ExtVehInfo EI2 
                WHERE EI2.RevisionDate = A.RevisionDate and EI2.LngCode = A.LngCode and EI2.InfoCode = EI03.ExtInfoCode AND EI2.codeA = A.codeA+A.TrimCode) IS NOT NULL)
            )
            OR exists
                  (
                  select top 1 * from EI02_ExtVehInfo z
                  inner join [LXXD-TRXI-PLO1].CADEAUX.dbo.EI03_VehExtInfo b on b.codeA = substring(z.codeA,1,11) and b.OptionTrims = substring(z.codeA,12,2) and b.ExtInfoCode = z.InfoCode 
                  inner join [LXXD-TRXI-PLO1].CADEAUX.dbo.EI04_VehExtInfoDetail c on c.codeA = b.codeA and c.ExtInfoCode = b.ExtInfoCode and b.RevCode = c.RevCode and c.ExtInfoUID = 2 and ExtInfoData = z.Info2 and c.ExtInfoVariation = b.ExtInfoVariation
                  where z.codeA = A.codeA+A.TrimCode and InfoCode = EI03.ExtInfoCode and LngCode = A.LngCode and Info2 = ei04.ExtInfoData
                  and ((z.Logic is null and b.Logic is not null) OR (z.Logic is not null and b.Logic is null) OR (z.Logic <> b.Logic))
                  )
        ) 


        and 
        EI03.revcode=(
                    SELECT MAX(RevCode) 
                    FROM [LXXD-TRXI-PLO1].CADEAUX.dbo.EI03_VehExtInfo  
                    WHERE codeA = EI03.codeA 
                    AND ExtInfoCode = EI03.ExtInfoCode 
                    AND ExtInfoVariation = EI03.ExtInfoVariation
                )

Answer 1

@Cindy，考虑到我的评论，并根据现有的信息，我想提出以下几点。

首先，，，您在代码中以不一致的方式使用名称codeA。

在#codesTable中，它表示基础字段的前11个字符。但是在代码中的其他地方，您重新截断了字段，显然没有意义。例如，在(E.codeA = substring(A.codeA,1,11)，A中的这个字段已经被子字符串编辑为11个字符。

还有很多地方可以重新组合这些值，例如(A.codeA + A.TrimCode) NOT IN...。在这样做的过程中，您为数据库创建了不必要的工作，混淆了索引，并且大大增加了代码中的混乱。

如果可能的话，尝试给每个人一个单独的名称，例如codeA_short和codeA_full，并将这两个值存储在#codesTable中。

如果数据库本身在codeA的含义上非常不一致，那么请对此进行清楚的注释--也许，创建派生表，其唯一目的是重命名底层表的列，以便它们在查询中更清晰。这种混叠通常不会给数据库带来额外的负担。

其次，您没有对列使用两个部分的名称，这让我(乍一看)猜测所引用的是哪些表。在对这种复杂性的查询中，绝对必须清楚。还必须为表提供更清晰的别名--使用a、b、c和z，或者根本不使用名称--尤其是当您在多个地方多次引用相同的表时，包括在子查询中，而且其中许多都共享同名列。

第三代，我可以建议像这样重构填充#codesTable的代码吗？这在功能上是等价的，但避免了重复的子字符串逻辑，在这个特定的上下文中浓缩并使其更具可读性(您可以看到相同的内容和每一行的不同之处)，并包括我关于存储"full“和"short”codeA列的建议。

INSERT INTO #CodesTable (CodeA_full, CodeA_short, TrimCode, RevisionDate, LngCode)
    SELECT DISTINCT
        CodeA                       AS CodeA_full
        ,SUBSTRING(CodeA, 1, 11)    AS CodeA_short
        ,SUBSTRING(CodeA, 12, 2)    AS TrimCode
        ,RevisionDate           
        ,LngCode
    FROM
        (
            SELECT CodeA, RevisionDate, LngCode FROM table_II_ModelOpts.dbo.table05_TRIM        UNION ALL
            SELECT CodeA, RevisionDate, LngCode FROM table_II_Translate.dbo.table05_TRIM        UNION ALL
            SELECT CodeA, RevisionDate, LngCode FROM table_II_Translate_CHYLR.dbo.table05_TRIM  UNION ALL
            SELECT CodeA, RevisionDate, LngCode FROM table_II_Translate_Fleet.dbo.table05_TRIM  UNION ALL
            SELECT CodeA, RevisionDate, LngCode FROM table_II_Translate_FCA.dbo.table05_TRIM    UNION ALL
            SELECT CodeA, RevisionDate, LngCode FROM table_II_Translate_GCA.dbo.table05_TRIM    UNION ALL
            SELECT CodeA, RevisionDate, LngCode FROM table_II_Translate_Pre92.dbo.table05_TRIM
        ) AS all_a_codes;

最后在您的主选择中执行15个几乎相同的相关子查询。在没有查询计划的情况下，我不能确定，但我认为这种方法在性能上是可怕的。考虑首先准备一个旋转数据表(我还没有包含最后的select语句)：

WITH ei_04_pivot_source AS
(
    SELECT
        CodeA
        ,RevCode
        ,ExtInfoVariation
        ,ExtInfoCode
        ,ExtInfoUID
        ,ExtInfoData 
    FROM [LXXD-TRXI-PLO1].CADEAUX.dbo.EI04_VehExtInfoDetail AS ei_04
)
,ei_04_pivoted_data AS
(
    SELECT * FROM ei_04_pivot_source PIVOT (
        MAX(ExtInfoData)
        FOR ExtInfoUID IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15])
    ) AS pvt
)
,ei_04_final_pivot AS
(
    SELECT
        CodeA
        ,RevCode
        ,ExtInfoVariation
        ,ExtInfoCode
        ,ISNULL([1],'') AS Info1
        ,ISNULL([2],'') AS Info2
        ,ISNULL([3],'') AS Info3
        ,ISNULL([4],'') AS Info4
        ,ISNULL([5],'') AS Info5
        ,ISNULL([6],'') AS Info6
        ,ISNULL([7],'') AS Info7
        ,SUBSTRING(ISNULL([8],''),1,250) AS Info8
        ,ISNULL([9],'') AS Info9
        ,ISNULL([10],'') AS Info10
        ,ISNULL([11],'') AS Info11
        ,ISNULL([12],'') AS Info12
        ,ISNULL([13],'') AS Info13
        ,ISNULL([14],'') AS Info14
        ,ISNULL([15],'') AS Info15
    FROM ei_04_pivoted_data
)

然后，可以将其左转到最后一个查询中，为您提供如下所示的15个"Info“列：

LEFT JOIN
    ei_04_final_pivot AS ei_04_pd
    ON (ei_04_pd.CodeA = a.CodeA)
    AND (ei_04_pd.RevCode = a.RevCode)
    AND (ei_04_pd.ExtInfoVariation = a.ExtInfoVariation)
    AND (ei_04_pd.ExtInfoCode = a.ExtInfoCode)

您的问题的最终解决方案可能是首先像这样将查询分离开来，并试图找出是否有某些特定的部分本身表现不佳。

主查询的WHER-子句非常复杂，几乎无法理解，特别是没有深入了解(或说明)其各个部分的功能或意义，确实需要进行实质性重构。