How to remove the joins in this slow SQL

Question

I have the following (obfuscated) SQL running on SQL Server 2012 and need to significantly improve its performance. It works, but sometimes takes more than 60s to return.

I would like to extract the JOINS but this post seems to indicate that this will not be possible (because of things like MIN and MAX) - so how can improve the performance and get these joins simplified/improved?

SELECT
    wm.id, wm.uid, wm.em, wm.fn, wm.ln, c, y, RTRIM(LTRIM(yCode)) AS yCode, wm.d1, ISNULL(wm.ffn, wm.pp) as ffn, wm.ada,
    case
        when wm.mss & 2=2
        then 'false'
        else 'true'
    end AS isa,
    (
        SELECT ', '+RTRIM(p1.cKey)
        FROM profile p1
            inner join loc stl on p1.cKey=stl.cKey
        WHERE p1.id = wm.id and p1.s = 'A'
        FOR XML PATH('')
    ) [lst],
    lishc.[lstCount],
    TotalCount = COUNT(*) OVER(),
    la.lsa, wskp.cKey AS pid
FROM wmm wm
    LEFT JOIN profile p1 ON wm.id = p1.id
    LEFT JOIN (
        SELECT UA.id, CONVERT(datetime, UA.ins, 1) As lsa
        FROM actlog UA
            INNER JOIN (
                select id, max(ins) as laa
                from actlog
                group by id
            ) UAJ on UA.id=UAJ.id and UA.ins=UAJ.laa
    ) la on la.id=wm.id
    LEFT JOIN (
        SELECT id, cKey FROM (
            SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY d1 desc) AS ROWNUM 
            FROM keypro where sc = 'SAP' AND cKeyDesc = 'SAP Agent ID'
        ) x WHERE ROWNUM = 1
    ) wskp ON wskp.id = wm.id
    LEFT JOIN (
        (SELECT p1.id ,COUNT(p1.cKey) AS [lstCount]
        FROM profile p1
            inner join loc stl on p1.cKey=stl.cKey
        where p1.s = 'A'
        GROUP BY p1.id)
    ) lishc ON lishc.id = wm.id
WHERE (@id = 0 OR wm.id = @id)
    AND (@uid IS NULL OR wm.uid LIKE '%' + @uid + '%')
    AND (@c IS NULL OR wm.c LIKE '%' + @c + '%')
    AND (@fn IS NULL OR wm.fn LIKE '%' + @fn + '%')
    AND (@ln IS NULL OR wm.ln LIKE '%' + @ln + '%')
    AND (@em IS NULL OR wm.em LIKE '%' + @em + '%')
    AND (@ffn IS NULL OR (wm.ffn LIKE '%' + @ffn + '%' OR wm.pp LIKE '%' + @ffn + '%'))                                                                
    AND (@pid IS NULL OR wskp.cKey LIKE '%' + @pid + '%' )
    AND (@Date1 IS NULL OR (CAST(wm.d1 AS DATE) BETWEEN CAST(@Date1 AS DATE) AND CAST(@Date2 AS DATE)))
    AND (@lsa1 IS NULL OR (CAST(la.lsa AS DATE) BETWEEN CAST(@lsa1 AS DATE) AND CAST(@lsa2 AS DATE)))
    AND (@Active IS NULL OR (wm.mss & 2 != 2))
    AND (@Inactive IS NULL OR (wm.mss & 2 = 2))
    AND (@External IS NULL OR (wm.ada = 'biz'))
    AND (@Internal IS NULL OR (wm.ada <> 'biz'))
    AND (@ApplyyFilter =0  OR (wm.yCode IN (SELECT @yCode WHERE 1 = 0))) 
    AND (@ApplylstFilter = 0 OR(p1.cKey IN (SELECT @ShipToList WHERE 1 = 0)))
    AND (@ApplylstFilter = 0 OR(p1.s = 'A'))
    AND (@ApplyNoFilter = 0 OR (lishc.[lstCount] is null))
    AND (@lstCount = 0 OR lishc.[lstCount] = @lstCount)
    AND (@ApplyLimitedFilter = 0 OR (wm.id IN (0)))
    AND (@ApplyMoreFilter = 0 OR (wm.id IN (SELECT @idss WHERE 1 = 0)))
GROUP BY wm.id, wm.uid, wm.em, wm.fn, wm.ln, y, yCode,c,wm.d1,wm.ffn,wm.mss,wm.ada, la.lsa, wskp.cKey, lishc.[lstCount], wm.pp
ORDER BY lsa DESC
OFFSET @PageOffset ROWS FETCH NEXT @PageSize ROWS ONLY

You have the problem here of having a catch-all query, which require different logic. Plus almost *every single one* of your parameters are wrapped in wild cards, making the query completely non-SARGable. I suspect that the JOINs are one of the smaller problems here. — Thom A, Mar 29 '21 at 16:45

David Browne - Microsoft · Accepted Answer · 2021-03-30T11:46:13.180

5

The quick hit here is to add OPTION (RECOMPILE) so SQL Server can eliminate the predicates that correspond to null parameters and create a new plan for each search.

And see, generally Dynamic Search Conditions in T‑SQL

The next thing to do is to get rid of the wildcard searches wherever possible.

And transform this

 (CAST(la.lsa AS DATE) BETWEEN CAST(@lsa1 AS DATE) AND CAST(@lsa2 AS DATE)))

into a SARGable pattern like

 la.lsa >= @lsa1 and la.lsa < @lsa2

Then start to pull this query apart, and hand-write separate queries for the most common or critical cases.

edited Mar 30 '21 at 11:46

answered Mar 29 '21 at 16:45

David Browne - Microsoft

80,331
6
39
67

+1 for `OPTION(RECOMPILE)`. SQL Server will then use constant folding to eliminate predicates where the result is known at compile time. – Dan Guzman Mar 29 '21 at 17:20
1

Why would `BETWEEN` be non-sargable? [This page](https://en.wikipedia.org/wiki/Sargable) explicitly lists it as SARGable. – Matt W Mar 30 '21 at 05:12
The `BETWEEN` is fine (although the replacement should be `>=` and `<=`), but the `CAST`s make it non-SARGable. – deroby Mar 30 '21 at 07:07
And the change from between to >=, < is because lsa might have a time component, so @lsa2 would be midnight on the next day. – David Browne - Microsoft Mar 30 '21 at 11:45
Let's say @lsa1 = '12 Nov 2020 15:30' and @lsa2 = '19 Nov 2020 10:01' then the original query would allow for '19 Nov 2020 18:05' to match but the `>=` / `<` clause will not! So even though I hadn't realised it yesterday, the replacement is NOT equivalent when the data has time-parts... All in all, the problem here is mainly that we don't know the datatype of `lsa`, `@lsa1` and `@lsa2`. For all we know it could be strings with ISO 106 notation thus actually requiring the `CAST()`. Fun fact: if they are `datetime`s then the `Cast` to `date` will NOT break SARGability if I remember correctly. – deroby Mar 31 '21 at 19:34
The parameters would need to be adjusted. – David Browne - Microsoft Mar 31 '21 at 19:37

How to remove the joins in this slow SQL

1 Answers1