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

Answer 1

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.