SQL Server多TVVA表关联查询执行过慢，寻求优化方案

我有一条SQL Server查询语句，执行耗时数分钟。最初只关联到TVVA5.VVA_VAL时执行正常，但引入TVVA6后开始变慢，加入TVVA7后更慢，每新增一个TVVA相关列，查询速度就持续下降。测试发现，关联不超过5个TVVA列时执行流畅，求优化思路。

原查询代码：

SELECT 
                [TCRD].[CRD_REQ_ID] AS [requestId],
                [TCTP].[CTP_CDE] AS cardType,
                [TCHD].[CHD_COD_EXT] AS codeCardHolder,
                [TCHD].[CHD_FRST_NAMES] AS firstNames,
                [TCHD].[CHD_INI] AS initials,
                [TCHD].[CHD_PFX_LST_NAME] AS prefixLastName,
                [TCHD].[CHD_LST_NAME] AS lastName,
                [TCHD].[CHD_TTL_PFX] AS titlePrefix,
                [TCHD].[CHD_TTL_SFX] AS titleSuffix,
                [TCHD].[CHD_DOB] dateOfBirth,
                [TCRD].[CRD_VAL_DTE] AS cardExpiryDate,
                [TCRD].[CRD_ISS_DTE] AS cardIssueDate,
                [TCHD].[CHD_NAT_CODE] AS natCode,
                [TCHD].[CGD_GDR_CDE] AS genderCode,

                [TPIC].[PIC_VAL] AS picture,
                [TSIG].[SIG_VAL] AS [signature],

                [TCRD].[CRD_NAME_ON_CARD] AS nameOnCard,
                [TORG].[ORG_CDE] AS organizationCode,
                [TNAT].[NAT_DESC_AR] AS nationalityArabic,
                TORG.ORG_FULL_NAME issuingAuthority,

                TVVA1.VVA_VAL nameArabic,
                TVVA2.VVA_VAL docmentType,
                TVVA3.VVA_VAL docmentNumber,
                TVVA4.VVA_VAL passportNumber,
                TVVA5.VVA_VAL phoneNumber,
                TVVA6.VVA_VAL professionEnglish,
                TVBV1.VBV_VAL passportImage,
                TVVA7.VVA_VAL cardPersonalizationDate,
                TVVA8.VVA_VAL printerSerialNumber,
                TVVA9.VVA_VAL placeOfBirthArabic,
                TVVA10.VVA_VAL addressInQatarArabic,
                TVVA11.VVA_VAL sponsorNameEnglish,
                TVVA12.VVA_VAL sponsorNameArabic,
                TVVA13.VVA_VAL residencyType
            FROM        
                TCHD                
            INNER JOIN
                TCRD
            ON
                [TCHD].[CHD_ID] = [TCRD].[CRD_CHD_ID]
            INNER JOIN
                TCTP
            ON
                [TCRD].[CRD_CTP_ID] = [TCTP].[CTP_ID]
            INNER JOIN
                TNAT
            ON
                [TCHD].[CHD_NAT_CODE] = [TNAT].[NAT_CODE]
            INNER JOIN
                TORG
            ON
                [TCRD].[CRD_ORG_ID] = [TORG].[ORG_ID]
            INNER JOIN
                TPIC                        
                cross apply (select [TPIC].[PIC_VAL] AS '*' for xml path('')) P ([picture])
            ON
                [TCRD].[CRD_ID] = [TPIC].[PIC_CRD_ID]
            INNER JOIN              
                TSIG
                cross apply (select [TSIG].[SIG_VAL] AS '*' for xml path('')) S ([signature])
            ON
                [TCRD].[CRD_ID] = [TSIG].[SIG_CRD_ID]
            INNER JOIN
                TVVA TVVA1
            ON
                TVVA1.VVA_PK_VAL = TCHD.CHD_ID 
            AND
                TVVA1.VVA_DVR_ID = (SELECT TDVR.DVR_ID FROM TDVR WHERE TDVR.DVR_NAME = 'NAME_ARABIC' )
            INNER JOIN
                TVVA TVVA2
            ON
                TVVA2.VVA_PK_VAL = TCHD.CHD_ID 
            AND 
                TVVA2.VVA_DVR_ID = (SELECT TDVR.DVR_ID FROM TDVR WHERE TDVR.DVR_NAME = 'DOCUMENT_TYPE' )
            INNER JOIN
                TVVA TVVA3
            ON
                TVVA3.VVA_PK_VAL = TCHD.CHD_ID
            AND 
                TVVA3.VVA_DVR_ID = (SELECT TDVR.DVR_ID FROM TDVR WHERE TDVR.DVR_NAME = 'DOCUMENT_NUMBER' )
            INNER JOIN
                TVVA TVVA4
            ON
                TVVA4.VVA_PK_VAL = TCHD.CHD_ID
            AND 
                TVVA4.VVA_DVR_ID = (SELECT TDVR.DVR_ID FROM TDVR WHERE TDVR.DVR_NAME = 'PASSPORT_NUMBER' )
            INNER JOIN
                TVVA TVVA5
            ON
                TVVA5.VVA_PK_VAL = TCHD.CHD_ID
            AND 
                TVVA5.VVA_DVR_ID = (SELECT TDVR.DVR_ID FROM TDVR WHERE TDVR.DVR_NAME = 'PHONE_NUMBER' )
            INNER JOIN
                TVVA TVVA6
            ON
                TVVA6.VVA_PK_VAL = TCHD.CHD_ID
            AND 
                TVVA6.VVA_DVR_ID = (SELECT TDVR.DVR_ID FROM TDVR WHERE TDVR.DVR_NAME = 'PROFESSION_ENGLISH' )
            INNER JOIN
                TVVA TVVA7
            ON
                TVVA7.VVA_PK_VAL = TCRD.CRD_ID
            AND 
                TVVA7.VVA_DVR_ID = (SELECT TDVR.DVR_ID FROM TDVR WHERE TDVR.DVR_NAME = 'CARD_PERSONALIZATION_DATE' )
            INNER JOIN
                TVBV TVBV1
                cross apply (select TVBV1.VBV_VAL AS '*' for xml path('')) PP ([passportImage])
            ON
                TVBV1.VBV_PK_VAL = TCHD.CHD_ID      
            AND 
                TVBV1.VBV_DVR_ID = (SELECT TDVR.DVR_ID FROM TDVR WHERE TDVR.DVR_NAME = 'PASSPORT_IMAGE' )
            INNER JOIN
                TVVA TVVA8
            ON
                TVVA8.VVA_PK_VAL = TCRD.CRD_ID
            AND 
                TVVA8.VVA_DVR_ID = (SELECT TDVR.DVR_ID FROM TDVR WHERE TDVR.DVR_NAME = 'PRINTER_SERIAL_NUMBER' )
            INNER JOIN
                TVVA TVVA9
            ON
                TVVA9.VVA_PK_VAL = TCHD.CHD_ID
            AND 
                TVVA9.VVA_DVR_ID = (SELECT TDVR.DVR_ID FROM TDVR WHERE TDVR.DVR_NAME = 'PLACE_OF_BIRTH_ARABIC' )
            INNER JOIN
                TVVA TVVA10
            ON
                TVVA10.VVA_PK_VAL = TCHD.CHD_ID
            AND 
                TVVA10.VVA_DVR_ID = (SELECT TDVR.DVR_ID FROM TDVR WHERE TDVR.DVR_NAME = 'ADDRESS_IN_QATAR_ARABIC' )
            INNER JOIN
                TVVA TVVA11
            ON
                TVVA11.VVA_PK_VAL = TCHD.CHD_ID
            AND 
                TVVA11.VVA_DVR_ID = (SELECT TDVR.DVR_ID FROM TDVR WHERE TDVR.DVR_NAME = 'SPONSOR_NAME_ENGLISH' )
            INNER JOIN
                TVVA TVVA12
            ON
                TVVA12.VVA_PK_VAL = TCHD.CHD_ID
            AND 
                TVVA12.VVA_DVR_ID = (SELECT TDVR.DVR_ID FROM TDVR WHERE TDVR.DVR_NAME = 'SPONSOR_NAME_ARABIC' )
            INNER JOIN
                TVVA TVVA13
            ON
                TVVA13.VVA_PK_VAL = TCHD.CHD_ID
            AND 
                TVVA13.VVA_DVR_ID = (SELECT TDVR.DVR_ID FROM TDVR WHERE TDVR.DVR_NAME = 'RESIDENCY_TYPE' )
            WHERE                                       
                [TCRD].[CRD_REQ_ID] = 10720

优化思路 #

1. 用条件聚合替换多次TVVA表关联 #

多次JOIN同一张TVVA表会导致查询计划反复扫描表，性能随关联次数线性下降。改用条件聚合（行转列），只需扫描TVVA表1-2次就能获取所有需要的字段：

SELECT 
    [TCRD].[CRD_REQ_ID] AS [requestId],
    [TCTP].[CTP_CDE] AS cardType,
    [TCHD].[CHD_COD_EXT] AS codeCardHolder,
    [TCHD].[CHD_FRST_NAMES] AS firstNames,
    [TCHD].[CHD_INI] AS initials,
    [TCHD].[CHD_PFX_LST_NAME] AS prefixLastName,
    [TCHD].[CHD_LST_NAME] AS lastName,
    [TCHD].[CHD_TTL_PFX] AS titlePrefix,
    [TCHD].[CHD_TTL_SFX] AS titleSuffix,
    [TCHD].[CHD_DOB] dateOfBirth,
    [TCRD].[CRD_VAL_DTE] AS cardExpiryDate,
    [TCRD].[CRD_ISS_DTE] AS cardIssueDate,
    [TCHD].[CHD_NAT_CODE] AS natCode,
    [TCHD].[CGD_GDR_CDE] AS genderCode,
    [TPIC].[PIC_VAL] AS picture,
    [TSIG].[SIG_VAL] AS [signature],
    [TCRD].[CRD_NAME_ON_CARD] AS nameOnCard,
    [TORG].[ORG_CDE] AS organizationCode,
    [TNAT].[NAT_DESC_AR] AS nationalityArabic,
    TORG.ORG_FULL_NAME issuingAuthority,
    -- 从CHD关联的TVVA聚合结果取字段
    tvva_chd.nameArabic,
    tvva_chd.docmentType,
    tvva_chd.docmentNumber,
    tvva_chd.passportNumber,
    tvva_chd.phoneNumber,
    tvva_chd.professionEnglish,
    tvva_chd.placeOfBirthArabic,
    tvva_chd.addressInQatarArabic,
    tvva_chd.sponsorNameEnglish,
    tvva_chd.sponsorNameArabic,
    tvva_chd.residencyType,
    -- 从CRD关联的TVVA聚合结果取字段
    tvva_crd.cardPersonalizationDate,
    tvva_crd.printerSerialNumber,
    TVBV1.VBV_VAL passportImage
FROM        
    TCHD                
INNER JOIN TCRD ON [TCHD].[CHD_ID] = [TCRD].[CRD_CHD_ID]
INNER JOIN TCTP ON [TCRD].[CRD_CTP_ID] = [TCTP].[CTP_ID]
INNER JOIN TNAT ON [TCHD].[CHD_NAT_CODE] = [TNAT].[NAT_CODE]
INNER JOIN TORG ON [TCRD].[CRD_ORG_ID] = [TORG].[ORG_ID]
INNER JOIN TPIC ON [TCRD].[CRD_ID] = [TPIC].[PIC_CRD_ID]
INNER JOIN TSIG ON [TCRD].[CRD_ID] = [TSIG].[SIG_CRD_ID]
INNER JOIN TVBV TVBV1 
    ON TVBV1.VBV_PK_VAL = TCHD.CHD_ID      
    AND TVBV1.VBV_DVR_ID = (SELECT DVR_ID FROM TDVR WHERE DVR_NAME = 'PASSPORT_IMAGE')
-- 聚合关联CHD_ID的TVVA数据
LEFT JOIN (
    SELECT 
        VVA_PK_VAL,
        MAX(CASE WHEN VVA_DVR_ID = (SELECT DVR_ID FROM TDVR WHERE DVR_NAME = 'NAME_ARABIC') THEN VVA_VAL END) AS nameArabic,
        MAX(CASE WHEN VVA_DVR_ID = (SELECT DVR_ID FROM TDVR WHERE DVR_NAME = 'DOCUMENT_TYPE') THEN VVA_VAL END) AS docmentType,
        MAX(CASE WHEN VVA_DVR_ID = (SELECT DVR_ID FROM TDVR WHERE DVR_NAME = 'DOCUMENT_NUMBER') THEN VVA_VAL END) AS docmentNumber,
        MAX(CASE WHEN VVA_DVR_ID = (SELECT DVR_ID FROM TDVR WHERE DVR_NAME = 'PASSPORT_NUMBER') THEN VVA_VAL END) AS passportNumber,
        MAX(CASE WHEN VVA_DVR_ID = (SELECT DVR_ID FROM TDVR WHERE DVR_NAME = 'PHONE_NUMBER') THEN VVA_VAL END) AS phoneNumber,
        MAX(CASE WHEN VVA_DVR_ID = (SELECT DVR_ID FROM TDVR WHERE DVR_NAME = 'PROFESSION_ENGLISH') THEN VVA_VAL END) AS professionEnglish,
        MAX(CASE WHEN VVA_DVR_ID = (SELECT DVR_ID FROM TDVR WHERE DVR_NAME = 'PLACE_OF_BIRTH_ARABIC') THEN VVA_VAL END) AS placeOfBirthArabic,
        MAX(CASE WHEN VVA_DVR_ID = (SELECT DVR_ID FROM TDVR WHERE DVR_NAME = 'ADDRESS_IN_QATAR_ARABIC') THEN VVA_VAL END) AS addressInQatarArabic,
        MAX(CASE WHEN VVA_DVR_ID = (SELECT DVR_ID FROM TDVR WHERE DVR_NAME = 'SPONSOR_NAME_ENGLISH') THEN VVA_VAL END) AS sponsorNameEnglish,
        MAX(CASE WHEN VVA_DVR_ID = (SELECT DVR_ID FROM TDVR WHERE DVR_NAME = 'SPONSOR_NAME_ARABIC') THEN VVA_VAL END) AS sponsorNameArabic,
        MAX(CASE WHEN VVA_DVR_ID = (SELECT DVR_ID FROM TDVR WHERE DVR_NAME = 'RESIDENCY_TYPE') THEN VVA_VAL END) AS residencyType
    FROM TVVA
    GROUP BY VVA_PK_VAL
) tvva_chd ON tvva_chd.VVA_PK_VAL = TCHD.CHD_ID
-- 聚合关联CRD_ID的TVVA数据
LEFT JOIN (
    SELECT 
        VVA_PK_VAL,
        MAX(CASE WHEN VVA_DVR_ID = (SELECT DVR_ID FROM TDVR WHERE DVR_NAME = 'CARD_PERSONALIZATION_DATE') THEN VVA_VAL END) AS cardPersonalizationDate,
        MAX(CASE WHEN VVA_DVR_ID = (SELECT DVR_ID FROM TDVR WHERE DVR_NAME = 'PRINTER_SERIAL_NUMBER') THEN VVA_VAL END) AS printerSerialNumber
    FROM TVVA
    GROUP BY VVA_PK_VAL
) tvva_crd ON tvva_crd.VVA_PK_VAL = TCRD.CRD_ID
WHERE                                       
    [TCRD].[CRD_REQ_ID] = 10720

2. 预获取DVR_ID，避免重复子查询 #

原查询每个TVVA关联都执行一次SELECT TDVR.DVR_ID，会重复查询TDVR表。可以提前把需要的DVR_ID存入变量，减少重复查询：

-- 提前定义所有需要的DVR_ID变量
DECLARE @NameArabicId INT = (SELECT DVR_ID FROM TDVR WHERE DVR_NAME = 'NAME_ARABIC');
DECLARE @DocumentTypeId INT = (SELECT DVR_ID FROM TDVR WHERE DVR_NAME = 'DOCUMENT_TYPE');
DECLARE @DocumentNumberId INT = (SELECT DVR_ID FROM TDVR WHERE DVR_NAME = 'DOCUMENT_NUMBER');
DECLARE @PassportNumberId INT = (SELECT DVR_ID FROM TDVR WHERE DVR_NAME = 'PASSPORT_NUMBER');
DECLARE @PhoneNumberId INT = (SELECT DVR_ID FROM TDVR WHERE DVR_NAME = 'PHONE_NUMBER');
DECLARE @ProfessionEnglishId INT = (SELECT DVR_ID FROM TDVR WHERE DVR_NAME = 'PROFESSION_ENGLISH');
DECLARE @CardPersonalizationDateId INT = (SELECT DVR_ID FROM TDVR WHERE DVR_NAME = 'CARD_PERSONALIZATION_DATE');
DECLARE @PrinterSerialNumberId INT = (SELECT DVR_ID FROM TDVR WHERE DVR_NAME = 'PRINTER_SERIAL_NUMBER');
DECLARE @PlaceOfBirthArabicId INT = (SELECT DVR_ID FROM TDVR WHERE DVR_NAME = 'PLACE_OF_BIRTH_ARABIC');
DECLARE @AddressInQatarArabicId INT = (SELECT DVR_ID FROM TDVR WHERE DVR_NAME = 'ADDRESS_IN_QATAR_ARABIC');
DECLARE @SponsorNameEnglishId INT = (SELECT DVR_ID FROM TDVR WHERE DVR_NAME = 'SPONSOR_NAME_ENGLISH');
DECLARE @SponsorNameArabicId INT = (SELECT DVR_ID FROM TDVR WHERE DVR_NAME = 'SPONSOR_NAME_ARABIC');
DECLARE @ResidencyTypeId INT = (SELECT DVR_ID FROM TDVR WHERE DVR_NAME = 'RESIDENCY_TYPE');
DECLARE @PassportImageId INT = (SELECT DVR_ID FROM TDVR WHERE DVR_NAME = 'PASSPORT_IMAGE');

-- 查询中直接使用变量
MAX(CASE WHEN VVA_DVR_ID = @NameArabicId THEN VVA_VAL END) AS nameArabic

3. 添加针对性索引 #

• TVVA表创建复合覆盖索引：CREATE NONCLUSTERED INDEX IX_TVVA_PK_DVR ON TVVA(VVA_PK_VAL, VVA_DVR_ID) INCLUDE(VVA_VAL);，让聚合查询无需回表。
• TVBV表创建复合覆盖索引：CREATE NONCLUSTERED INDEX IX_TVBV_PK_DVR ON TVBV(VBV_PK_VAL, VBV_DVR_ID) INCLUDE(VBV_VAL);