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