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
Answers
The slowness you're experiencing is likely due to the large number of joins you have in your query, especially when joining multiple TVVA tables. Additionally, using subqueries in the join conditions can also impact performance negatively, especially if those subqueries are not optimized.
Here are a few optimization strategies you can try:
-
Use CTEs (Common Table Expressions): Instead of joining the TVVA tables separately using subqueries, you can use CTEs to pre-filter the TVVA data and then join them with the main query. This can sometimes improve performance by reducing the number of times the subqueries are executed.
-
Indexing: Ensure that the columns being used in joins and where clauses are properly indexed. This can significantly improve query performance, especially for large tables.
-
Optimize XML Path Operations: If the XML path operations used to concatenate values from multiple rows into a single string are causing performance issues, consider using a different approach to concatenate values, such as
STRING_AGGin SQL Server or array aggregation functions in PostgreSQL. -
Batch Processing: If the query is still slow after optimization, consider breaking it down into smaller batches and processing them separately. This can help reduce the overall load on the database server.
Here's a simplified example of how you can use CTEs to optimize the query:
WITH TVVA_CTE AS (
SELECT
VVA_PK_VAL,
VVA_VAL,
VVA_DVR_ID
FROM
TVVA
WHERE
DVR_NAME IN (
'NAME_ARABIC', 'DOCUMENT_TYPE', 'DOCUMENT_NUMBER', 'PASSPORT_NUMBER', 'PHONE_NUMBER',
'PROFESSION_ENGLISH', 'CARD_PERSONALIZATION_DATE', 'PRINTER_SERIAL_NUMBER',
'PLACE_OF_BIRTH_ARABIC', 'ADDRESS_IN_QATAR_ARABIC', 'SPONSOR_NAME_ENGLISH',
'SPONSOR_NAME_ARABIC', 'RESIDENCY_TYPE'
)
)
SELECT
...
FROM
TCHD
INNER JOIN
TCRD
ON
[TCHD].[CHD_ID] = [TCRD].[CRD_CHD_ID]
INNER JOIN
...
INNER JOIN
TVVA_CTE 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
...
You can apply similar optimizations to other parts of the query to improve its performance. Remember to analyze the execution plan and monitor query performance after making changes to ensure they have the desired effect.
