Hi,
Below query is performing slow in last two lines i commented. I believe it is ISNULL and CASE function in where clause which is making them slow. Any suggestions please?
DECLARE @statusId INT=13
DECLARE @isMinorDraft BIT=0
DECLARE @countryId INT = 76
DECLARE @brandId INT = NULL
DECLARE @universeId INT = NULL
DECLARE @languageId INT = NULL
DECLARE @partnerCode NVARCHAR(200) = null
DECLARE @partnerName NVARCHAR(200) = NULL
--DECLARE @triggerStateId = Select fkTriggerStateId from PartnerTemplate
Select distinct TOP 1000
p.PArtnerId AS OwnerId,
p.PartnerName + case when pl.LocationName is null then '' else ' - ' + pl.LocationName end AS OwnerName,
isnull(pt.PartnerTemplateId,0) AS TemplateId,
pdt.fkDataTemplateId AS DataTemplateId,
dt.DataTemplateName AS DataTemplateName,
l.LanguageName AS LanguageName,
l.LanguageId AS LanguageId,
pt.fkTriggerStateId
FROM
Partner p
JOIN PartnerProduct pp ON p.PartnerId = pp.fkPartnerId and pp.Active =1
JOIN product ppp ON ppp.ProductId = pp.fkProductId AND ppp.fkTriggerStateId = 4
JOIN ProductUniverse cpu ON ppp.ProductId = cpu.fkProductId
JOIN RelatedProduct rp ON rp.fkProduct1Id = ppp.ProductId and rp.fkRelationTypeId = 20 AND rp.Active = 1
JOIN ProductDataTemplate pdt ON pdt.fkProductId = rp.fkProductId and pdt.isPartnerTemplate =1 and (pdt.fkUniverseId is null or pdt.fkUniverseId = cpu.fkUniverseId )
JOIN DataTemplate dt ON pdt.fkDataTemplateId = dt.DataTemplateId
JOIN Language l ON l.LanguageId = pdt.fkLanguageId
left join PartnerTemplate pt on p.PartnerId = pt.fkPartnerId and pt.fkDataTemplateId = pdt.fkDataTemplateId
and pt.IsDeleted = 0 and pt.fkLanguageId = pdt.fkLanguageId
LEFT JOIN ProductUniverse pu ON pu.fkProductId = rp.fkProductId
LEFT JOIN ProductCountry pc ON pc.fkProductId = rp.fkProductId
LEFT JOIN ProductBrand pb ON pb.fkProductId = rp.fkProductId
LEFT JOIN PartnerLocation pl ON p.PartnerId = pl.fkPartnerId and pl.Active = 1
WHERE
(@languageId IS NULL OR @languageId = pdt.fkLanguageId)
AND (@partnerCode IS NULL OR @partnerCode = p.PartnerCode )
AND (@partnerName IS NULL OR p.PartnerName like '%' + @partnerName + '%')
AND (@universeId IS NULL OR @universeId = pu.fkUniverseId)
AND (@countryId IS NULL OR @countryId = pc.fkCountryId)
AND (@brandId IS NULL OR @brandId = pb.fkBrandId)
--AND ISNULL(pt.fkTriggerStateId,12) = @statusId --PROBLEM, It is very slow
--AND (CASE WHEN pt.fkPartnerTemplateId IS NULL THEN 0 ELSE 1 END) = @isMinorDraft -- PROBLEM, It is very slow
AND p.PartnerStatus = 1
group by
p.PartnerId,
p.Partnername,
pl.LocationName,
pt.PartnerTemplateId,
pdt.fkDataTemplateId,
dt.DataTemplateName,
l.LanguageName,
l.LanguageId,
pt.fkTriggerStateId