Why is this SQL Server query so slow?

Question

This query takes 4 seconds to run, and it returns about 62000 rows. @c, @p and @a are strings made up of ints, separated by commas or empty, based on what needs to be filtered... unfortunately this can't be changed.

I have tried to run this without the filters and it does not make a difference. I am fairly new to SQL.

The 3 common table expressions are there to take extra lines of code out.

I would like this to be able to run under a second if at all possible

DECLARE @UId int = '817',
        @c varchar(max) = '',
        @p varchar(max) = '',
        @a varchar(max) = ''

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

IF OBJECT_ID('tempdb..#FilteredData') IS NOT NULL 
     DROP TABLE #FilteredData;

WITH cteuag AS 
(
    SELECT
        uag.CId,
        uag.AuthorIsatIonGroupId,
        rn = ROW_NUMBER() OVER (PARTITION BY uag.[UId], uag.CId, uag.AuthorIsatIonGroupId ORDER BY uag.CId)
    FROM 
        uag
    WHERE 
        uag.[UId] = @UId
        AND uag.IsDeleted = 0
        AND (@c = '' OR (@c LIKE CONVERT(varchar(4), uag.CId)
                         OR @c LIKE '%,' + CONVERT(varchar(4), uag.CId) + ',%'
                         OR @c LIKE CONVERT(varchar(4), uag.CId) + ',%'
                         OR @c LIKE '%,' + CONVERT(varchar(4), uag.CId))
            )
),
cteuc AS
(
    SELECT
        uc.CId,
        cteuag.AuthorIsatIonGroupId,
        uc.[UId],
        i.FirstName + Iif(i.MiddleName IS NOT NULL, ' ' + i.MiddleName + ' ', ' ') + i.Surname AS [AName],
        rn = ROW_NUMBER() OVER (PARTITION BY uc.CId, uc.[UId] ORDER BY uc.ModifiedAt)
    FROM 
        uc
    INNER JOIN 
        cteuag ON cteuag.CId = uc.CId
               AND uc.IsDeleted = 0
               AND (@a = '' OR (@a LIKE CONVERT(varchar(4), uc.[UId])
                                OR @a LIKE '%,' + CONVERT(varchar(4), uc.[UId]) + ',%'
                                OR @a LIKE CONVERT(varchar(4), uc.[UId]) + ',%'
                                OR @a LIKE '%,' + CONVERT(varchar(4), uc.[UId]))
                   )
    INNER JOIN 
        ui ON ui.[UId] = uc.[UId]
           AND ui.IsDeleted = 0
    INNER JOIN 
        i ON i.PId = ui.IndividualId
),
ctel AS
(
    SELECT
        la.LId,
        p.CId,
        la.[UId],
        rn = ROW_NUMBER() OVER (PARTITION BY la.LId ORDER BY la.DateUpdated DESC)
    FROM  
        la 
    INNER JOIN 
        p ON p.Id = la.PId
          AND p.IsDeleted = 0
          AND la.IsDeleted = 0
          AND la.[UId] IS NOT NULL
    INNER JOIN 
        l ON la.LId = l.Id
          AND l.IsDeleted = 0
)
SELECT
    c.Id AS CId,
    c.[Name] AS CName,
    u.Id AS AId,
    cteuc.AName AS AName,
    ctel.LId AS LId,
    qh.Id AS QhId,
    q.Id AS QId,
    q.IsTrue AS isTrue,
    p.Id AS PId,
    p.[Name] AS PName,
    qi.FinalCalculation AS Calculation
INTO 
    #FilteredData
FROM 
    c
INNER JOIN 
    cteuc ON cteuc.CId = c.Id
          AND cteuc.Rn = 1
          AND c.IsDeleted = 0
LEFT OUTER JOIN 
    u ON u.Id = Iif(cteuc.AuthorIsatIonGroupId = 1, @UId, cteuc.[UId])
      AND u.IsDeleted = 0
LEFT OUTER JOIN 
    ctel ON ctel.CId = c.Id
         AND ctel.Rn = 1
         AND ctel.[UId] = u.Id
LEFT OUTER JOIN 
    (la
        INNER JOIN 
            qla ON qla.LeadActivityId = la.Id
                AND la.IsDeleted = 0
                AND la.ActivityTypeId = 4
        INNER JOIN 
            qh ON qla.QhId = qh.Id
               AND qh.IsDeleted = 0
               AND qh.IsReRated = 0
        LEFT OUTER JOIN 
            (q 
             INNER JOIN 
                 p ON p.Id = q.PId
                   AND q.IsDeleted = 0
                   AND p.IsDeleted = 0
        ) ON q.QhId = qh.Id) ON la.[UId] = u.Id
        AND la.LId = ctel.LId
        AND 
        (
            @p = ''
            OR 
            (
                @p LIKE CONVERT(varchar(4), p.Id)
                OR @p LIKE '%,' + CONVERT(varchar(4), p.Id) + ',%'
                OR @p LIKE CONVERT(varchar(4), p.Id) + ',%'
                OR @p LIKE '%,' + CONVERT(varchar(4), p.Id)
            )
        )
    LEFT OUTER JOIN 
    (
        SELECT
            qi1.QId,
            SUM(Iif(qi1.Calculation = 0, 0, qi1.Calculation + qi1.Extra)) AS FinalCalculation
        FROM qi
        WHERE qi1.IsDeleted = 0
        GROUP BY QId
    ) AS qi ON qi.QId = q.Id
WHERE 
    (
        @p = ''
        OR 
        (
            p.Id IS NOT NULL
            OR qh.Id IS NULL
        )
    )

SELECT * FROM #FilteredData

EDIT:

just to be clear, the above query does not run any slower than this one:

DECLARE @UId int = '817'

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

IF OBJECT_ID('tempdb..#FilteredData') IS NOT NULL DROP TABLE #FilteredData;

WITH cteuag AS 
(
    SELECT
        uag.CId,
        uag.AuthorIsatIonGroupId,
        rn = ROW_NUMBER() OVER (PARTITION BY uag.[UId], uag.CId, uag.AuthorIsatIonGroupId ORDER BY uag.CId)
    FROM uag
    WHERE uag.[UId] = @UId
        AND uag.IsDeleted = 0
),
cteuc AS
(
    SELECT
        uc.CId,
        cteuag.AuthorIsatIonGroupId,
        uc.[UId],
        i.FirstName + Iif(i.MiddleName IS NOT NULL, ' ' + i.MiddleName + ' ', ' ') + i.Surname AS [AName],
        rn = ROW_NUMBER() OVER (PARTITION BY uc.CId, uc.[UId] ORDER BY uc.ModifiedAt)
    FROM uc
        INNER JOIN cteuag ON cteuag.CId = uc.CId
            AND uc.IsDeleted = 0
        INNER JOIN ui ON ui.[UId] = uc.[UId]
            AND ui.IsDeleted = 0
        INNER JOIN i ON i.PId = ui.IndividualId
),
ctel AS
(
    SELECT
        la.LId,
        p.CId,
        la.[UId],
        rn = ROW_NUMBER() OVER (PARTITION BY la.LId ORDER BY la.DateUpdated DESC)
    FROM la 
        INNER JOIN p ON p.Id = la.PId
            AND p.IsDeleted = 0
            AND la.IsDeleted = 0
            AND la.[UId] IS NOT NULL
        INNER JOIN l ON la.LId = l.Id
            AND l.IsDeleted = 0
)
SELECT
    c.Id AS CId,
    c.[Name] AS CName,
    u.Id AS AId,
    cteuc.AName AS AName,
    ctel.LId AS LId,
    qh.Id AS QhId,
    q.Id AS QId,
    q.IsTrue AS isTrue,
    p.Id AS PId,
    p.[Name] AS PName,
    qi.FinalCalculation AS Calculation
INTO #FilteredData
FROM c
    INNER JOIN cteuc ON cteuc.CId = c.Id
        AND cteuc.Rn = 1
        AND c.IsDeleted = 0
    LEFT OUTER JOIN u ON u.Id = Iif(cteuc.AuthorIsatIonGroupId = 1, @UId, cteuc.[UId])
        AND u.IsDeleted = 0
    LEFT OUTER JOIN ctel ON ctel.CId = c.Id
        AND ctel.Rn = 1
        AND ctel.[UId] = u.Id
    LEFT OUTER JOIN 
    (
        la
        INNER JOIN qla ON qla.LeadActivityId = la.Id
            AND la.IsDeleted = 0
            AND la.ActivityTypeId = 4
        INNER JOIN qh ON qla.QhId = qh.Id
            AND qh.IsDeleted = 0
            AND qh.IsReRated = 0
        LEFT OUTER JOIN 
        (
            q 
            INNER JOIN p ON p.Id = q.PId
                AND q.IsDeleted = 0
                AND p.IsDeleted = 0
        ) ON q.QhId = qh.Id) ON la.[UId] = u.Id
        AND la.LId = ctel.LId
    LEFT OUTER JOIN 
    (
        SELECT
            qi1.QId,
            SUM(Iif(qi1.Calculation = 0, 0, qi1.Calculation + qi1.Extra)) AS FinalCalculation
        FROM qi
        WHERE qi1.IsDeleted = 0
        GROUP BY QId
    ) AS qi ON qi.QId = q.Id

SELECT * FROM #FilteredData

so I know it is not the comma delimited values used as filters, or the leading wild cards.

Having leading wild cards is going to ruin any query's performance unless you have some kind of full text index on the column. — Larnu, Apr 19 '18 at 14:45
Do your colums have index? There are so many sub-querys with Left Joins and searchs with Like, thats may be one factor i guess — WiLLyxVKei, Apr 19 '18 at 14:46
Look at the duplicate question and use an answer that involves splitting the string variables and joining rather than using LIKE. — Tab Alleman, Apr 19 '18 at 14:48
The normal stuff, looking into execution plan and `statistics io` output might also give an indication what's taking most of the time — James Z, Apr 19 '18 at 14:49
This is slow because you have nonSARGable predicates all over the place. Leading wildcards, wrapping columns in functions. And be careful using uncommitted transaction level. That can produce some really fun bugs that are impossible to track down. It is like putting nolock on every single table. And that query hint is far more sinister than most people realize. https://blogs.sentryone.com/aaronbertrand/bad-habits-nolock-everywhere/ — Sean Lange, Apr 19 '18 at 15:12
@SeanLange can you give an example of a nonSARGable predicate, and maybe an alternate way to get the same result with the one example? also unfortunately I don't have a choice on whether or not to use uncommitted transaction level - I was told to — questions, Apr 20 '18 at 06:46
Use execution plans to identify which steps are slow? Use the profiler? Build up your query one expression or join at a time until you find out which parts make it run slowly? Then make a MCVE? https://stackoverflow.com/help/mcve — MatBailie, Apr 20 '18 at 07:04
@MatBailie so i started testing as you suggested and found that the time it took to execute was halved when i took out the two last left outer joins - the ones with the sub joins. this made the output closer to 7000 rows. interestingly enough when i put those tables back in but didn't include the columns for the result it took the same amount of time.... when i compared the execution plan, it was the same as the original queries (the 4 second one). could printing out the 62000 rows be what is taking so much time? — questions, Apr 20 '18 at 07:43
@MatBailie also should i include a picture of the execution plan in the question? it is huge and I don't know how to display so that it is legible — questions, Apr 20 '18 at 07:45
Store the results in a table variable instead, so as to eliminate the network traffic aspect of the elapsed time. Compare the execution plans for the two queries, pasting them here (https://www.brentozar.com/pastetheplan/) if you need assistance. — MatBailie, Apr 20 '18 at 07:46
@MatBailie checked the execution plans, pretty much the same. About using a table variable, the estimated output will be around 60000 rows, and I thought table variables where best used when there are under 1000 rows? — questions, Apr 20 '18 at 10:04
Show us the plans? I linked you a site that allows you to do that... The table-variable suggestion isn't to Use the table variable, but to output your results to there instead of over the network to your terminal; solely to eliminate network traffic from your elapsed time measurement. As trying that would have taken under a minute, I'm not sure what the benefit is in you asking "are you sure?" I think I'll move on now. — MatBailie, Apr 20 '18 at 10:11
Sure....as I said. Leading wildcards, wrapping columns in functions. Both of those are nonSARGable and cause index scans. As for using read uncommitted maybe you should suggest to your boss that he/she read that article. Essentially it means you want your data to mostly accurate most of the time. Things like duplicate and/or missing rows happens....and far more often that many folks realize in a busy system. — Sean Lange, Apr 20 '18 at 13:28
@SeanLange thanks!! showed the article, but he wants it anyway. Will change the query so that it does not wrap columns in functions or use Leading wildcards though and see what happens — questions, Apr 20 '18 at 13:45
It looks like you may be storing delimited strings in your database? This is not how relational data should work. It violates 1NF and causes all sorts of problems. — Sean Lange, Apr 20 '18 at 13:47
actually those are not from the database at all. they are a user input and unfortunately they come as a delimited string. I am gonna see if they can be changed to TVPs — questions, Apr 20 '18 at 13:54
Ahh that makes sense. You could probably use a string splitter then on your parameters to eliminate the need to use LIKE at all. It should also let you eliminate the need to cast your columns to char(4) all the time. Here is my choice for splitters. http://www.sqlservercentral.com/articles/Tally+Table/72993/ There are several other excellent choices here. http://sqlperformance.com/2012/07/t-sql-queries/split-strings — Sean Lange, Apr 20 '18 at 13:57

score 0 · Accepted Answer · answered Apr 20 '18 at 13:51

0

So it turns out it runs fine on another laptop, which means it was something to do with my laptop. Regardless, looking at the comments, this query could use a lot of refinement

answered Apr 20 '18 at 13:51

questions

111
1
1
7

Why is this SQL Server query so slow?

1 Answers1