Old SQL Query Optimization

Vote count:

0

I have a pre written SQL Query, which takes 45 minutes to actually run and show the data. It returns total 80000+ records. But it takes hell lot of time to run and show the data.

SELECT 
hq.QuoteHeaderId,
hq.HandsetQuoteId,
hq.QuoteDate,
t_PhoneAudit.PhoneModelId,
t_PhoneAudit.IMEI,
t_PhoneModel.ModelName,
t_PhoneBrand.Name As BrandName
, al.ActivityId
, par.Result
, al.CustomMessage,
al.[Description]
,thqai.Value AS AgentName
FROM [t_DynaGroupMissingRecordsProcessing] 
INNER JOIN t_HandsetQuote hq ON t_DynaGroupMissingRecordsProcessing.HandsetQuoteId = hq.HandsetQuoteId
INNER JOIN t_PhoneAudit ON t_PhoneAudit.PhoneAuditId = hq.QuotePhoneAuditId
INNER JOIN t_PhoneModel ON t_PhoneModel.PhoneModelId = t_PhoneAudit.PhoneModelId
INNER JOIN t_PhoneBrand ON t_PhoneBrand.PhoneBrandId = t_PhoneModel.PhoneBrandId
INNER JOIN 
(
    SELECT par.HandsetQuoteId, txt_Value AS ActivityId, 'OK' AS Result FROM t_PhoneAuditRetail par CROSS APPLY dbo.fn_ParseText2Table(par.Ok, ',') 
    UNION 
    SELECT par.HandsetQuoteId, txt_Value AS ActivityId, 'Fault' AS Result FROM t_PhoneAuditRetail par CROSS APPLY dbo.fn_ParseText2Table(par.Fault, ',') 
) par ON hq.HandsetQuoteId = par.HandsetQuoteId
INNER JOIN t_ActivityLocalization al ON par.ActivityId = al.ActivityId AND al.ContactChannelId IN      (SELECT ContactChannelId FROM t_ContactChannel WHERE Name IN ('Holland Retail', 'BelCompanyNPEY Retail', 'Belcompany Retail', 'HollandNPEY Retail'))
INNER JOIN t_ContactChannel CC ON al.ContactChannelId = CC.ContactChannelId
INNER JOIN t_testingProfiledetails ON t_testingProfiledetails.ActivityId = al.ActivityId AND (    IsVisibleForRetail = '1' OR t_testingProfiledetails.ActivityId IN ('93','97')) AND  t_testingProfiledetails.ProfileId IN (SELECT DefailtRetailProfileId FROM t_ContactChannel WHERE Name IN ('Holland Retail', 'BelCompanyNPEY Retail', 'Belcompany Retail', 'HollandNPEY Retail'))
LEFT OUTER JOIN t_HandsetQuoteAdditionalInfo thqai on thqai.HandsetQuoteId = hq.HandsetQuoteId and thqai.KeyName = 'AgentName'
WHERE [t_DynaGroupMissingRecordsProcessing].IsProcessed = 0 
AND CC.Name IN ('Holland Retail', 'BelCompanyNPEY Retail', 'Belcompany Retail', 'HollandNPEY  Retail')
ORDER BY hq.HandsetQuoteId

The main problem is with CC.Name IN ('Holland Retail', 'BelCompanyNPEY Retail', 'Belcompany Retail', 'HollandNPEY Retail') this statement.

If i keep on adding the name in the In Statement, the query becomes more and more slow.

Please help me to optimize this query.

asked 1 min ago

HarshSharma

122

Old SQL Query Optimization