sqlsql-servert-sqldatabase-performance

SQL Server query executes very slow


I have the following query that takes minutes to get executed. When I started writing this query, uptil TVVA5.VVA_VAL it was OK but when I introduced TVVA6 it became slow and when I introduced TVVA7 it became slower and keeps becoming slower as I add TVVA columns. I found that until any 5 TVVA columns it works well. Any idea to optimize this query.

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

PasteThePlan


Solution

  • Instead of joining again and again for every possible VVA_VAL, use conditional aggregation inside an APPLY or a join on a derived table.

            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,
    
                TVVA.*
            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
            CROSS APPLY (
              SELECT
                MIN(CASE WHEN TDVR.DVR_NAME = 'nameArabic' THEN TVVA.VVA_VAL END) nameArabic,
                MIN(CASE WHEN TDVR.DVR_NAME = 'docmentType' THEN TVVA.VVA_VAL END) docmentType,
                MIN(CASE WHEN TDVR.DVR_NAME = 'docmentNumber' THEN TVVA.VVA_VAL END) docmentNumber,
                MIN(CASE WHEN TDVR.DVR_NAME = 'passportNumber' THEN TVVA.VVA_VAL END) passportNumber,
                MIN(CASE WHEN TDVR.DVR_NAME = 'phoneNumber' THEN TVVA.VVA_VAL END) phoneNumber,
                MIN(CASE WHEN TDVR.DVR_NAME = 'professionEnglish' THEN TVVA.VVA_VAL END) professionEnglish,
                MIN(CASE WHEN TDVR.DVR_NAME = 'passportImage' THEN TVBV.VBV_VAL END) passportImage,
                MIN(CASE WHEN TDVR.DVR_NAME = 'cardPersonalizationDate' THEN TVVA.VVA_VAL END) cardPersonalizationDate,
                MIN(CASE WHEN TDVR.DVR_NAME = 'printerSerialNumber' THEN TVVA.VVA_VAL END) printerSerialNumber,
                MIN(CASE WHEN TDVR.DVR_NAME = 'placeOfBirthArabic' THEN TVVA.VVA_VAL END) placeOfBirthArabic,
                MIN(CASE WHEN TDVR.DVR_NAME = 'addressInQatarArabic' THEN TVVA.VVA_VAL END) addressInQatarArabic,
                MIN(CASE WHEN TDVR.DVR_NAME = 'sponsorNameEnglish' THEN TVVA.VVA_VAL END) sponsorNameEnglish,
                MIN(CASE WHEN TDVR.DVR_NAME = 'sponsorNameArabic' THEN TVVA.VVA_VAL END) sponsorNameArabic,
                MIN(CASE WHEN TDVR.DVR_NAME = 'residencyType' THEN TVVA.VVA_VAL END) residencyType
              FROM TVVA
              JOIN TDVR ON TVVA.VVA_DVR_ID = TDVR.DVR_ID
              WHERE
                TVVA.VVA_PK_VAL = TCHD.CHD_ID 
            ) TVVA
            WHERE                                       
                [TCRD].[CRD_REQ_ID] = 10720