none
Improve SQL query performance RRS feed

  • Question

  • 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


    Sarfraz Khan

    Thursday, January 23, 2020 11:01 AM

All replies

  • Using function on columns in WHERE clause filter prevents the usage of existing indexes, so re-write your query without function usage.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, January 23, 2020 11:13 AM
  • Thanks Olaf, 

    I understand it, however not sure how to avoid this ISNULL or CASE function. Can you show how to avoid it?


    Sarfraz Khan

    Thursday, January 23, 2020 11:50 AM
  • For this pattern:

    (@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)

    It is always a good idea to slap on
    OPTION (RECOMPILE)
    at the end of the query. The isull and the CASE may still cause problems, but that depends on there are indexes on the table.

    I'm also looking with suspicion on this:

    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

    How many rows can each LEFT JOIN produce? If they can produce at most one each, there is no issue. If each can produce multiple rows, there is a problem...


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, January 23, 2020 11:15 PM
  • Hi , 

    Please try following script. 

    --AND ISNULL(pt.fkTriggerStateId,) = @statusId --PROBLEM, It is very slow
    and (pt.fkTriggerStateId is null and pt.fkTriggerStateId=@statusId 
    or pt.fkTriggerStateId is not null and @statusId=12)
    --AND (CASE WHEN pt.fkPartnerTemplateId IS NULL THEN 0 ELSE 1 END) = @isMinorDraft -- PROBLEM, It is very slow
    and (pt.fkPartnerTemplateId IS NULL and @isMinorDraft=0 
    or pt.fkPartnerTemplateId IS not NULL and @isMinorDraft=1)

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 24, 2020 5:46 AM