3

I would like some advice on how I could optimize the performance of a query that is stored in a procedure.

Previously, it took 20 minutes and now we can get it in 13 minutes. But it's still a long time.

I've checked the indexes several times, updated the statistics too and got the 13 minutes running.

I also tried to replace the logic of LEFT JOIN with OUTER APPLY but performance deteriorated.

Strangely, when I removed the [ShowToUser] = 1 filter in the WHERE clause, in SSMS the query was very fast (all records have a value of 1), so I commented this line of code and I removed the indexes with this column, as I thought it unnecessary. But when processing via application, the performance was even worse!

SELECT COUNT([Id]) AS [Events],
    [ClientId],
    [PersonId],
    [FullName],
    [SuperiorId],
    [Manager],
    [ClientPhoneNumberId],
    [PhoneNumber],
    [Departament],
    [CostCenter],
    [CostCenterCode],
    [TelecomUserInitialDate],
    [TelecomUserEndDate],
    [TelecomUserId],
    [PhoneCompanyId],
    [PhoneCompanyName],
    (SUM([UserCost]) / COUNT(DISTINCT [ReferenceMonth])) AS [AverageCostMonth],
    MAX([ReferenceMonth]) AS [ReferenceMonth],
    CAST(SUM(CASE 
                WHEN [ReferenceMonth] = @referenceMonthEnd
                    THEN [UserCost]
                ELSE 0
                END) AS DECIMAL(18, 4)) AS [CostSumLastReference],
    Meta,
    ServicesValue,
    RegisteredArea,
    CASE 
        WHEN ServicesValue > 0
            THEN (
                    ServicesValue - CAST(SUM(CASE 
                                WHEN [ReferenceMonth] = @referenceMonthEnd
                                    THEN [UserCost]
                                ELSE 0
                                END) AS DECIMAL(18, 4))
                    ) * 100 / ServicesValue
        ELSE 0
        END AS ServicesPercent,
    CASE 
        WHEN Meta > 0
            THEN (
                    Meta - CAST(SUM(CASE 
                                WHEN [ReferenceMonth] = @referenceMonthEnd
                                    THEN [UserCost]
                                ELSE 0
                                END) AS DECIMAL(18, 4))
                    ) * 100 / Meta
        ELSE 0
        END AS MetaPercent
FROM (
    SELECT DISTINCT [t].[Id],
        [c].[Id] AS [ClientId],
        [p2].[Id] AS [PersonId],
        [p2].[FullName],
        [p2].[SuperiorId],
        [mg].[Fullname] AS [Manager],
        [cn].[Id] AS [ClientPhoneNumberId],
        [n].[Number] AS [PhoneNumber],
        [dp].[Name] AS [Departament],
        [tcc].[Name] AS [CostCenter],
        [tcc].[Code] AS [CostCenterCode],
        [tu].[InitialDate] AS [TelecomUserInitialDate],
        [tu].[EndDate] AS [TelecomUserEndDate],
        [a].[ReferenceMonth] AS [ReferenceMonth],
        [tu].[Id] AS [TelecomUserId],
        tu.goal AS Meta,
        tu.ServicesValue,
        tu.RegisteredArea,
        CASE 
            WHEN @phoneCompanyId IS NOT NULL
                THEN [cn].[PhoneCompanyId]
            ELSE NULL
            END AS [PhoneCompanyId],
        [cp].[TradingName] [PhoneCompanyName],
        CAST([t].[UserCost] AS DECIMAL(18, 4)) AS [UserCost]
    FROM [telecom].[CallDetailRecord] AS [t]
    LEFT JOIN [telecom].[TelecomBill] AS [a]
        ON [a].[Id] = [t].[TelecomBillId]
    LEFT JOIN [telecom].[TelecomBillAccount] AS [tba]
        ON [tba].[Id] = [a].[TelecomBillAccountId]
    LEFT JOIN [telecom].[TelecomBillAccountContract] AS [tbacon]
        ON [tbacon].[TelecomBillAccountId] = [tba].[Id]
    LEFT JOIN [doc].[PersonContract] AS [pcontr]
        ON [pcontr].[Id] = [tbacon].[ContractId]
    LEFT JOIN [people].[Person] AS [p]
        ON [p].[Id] = [pcontr].[ContractorId]
    LEFT JOIN [customer].[Client] AS [c]
        ON [c].[PersonId] = [p].[Id]
    LEFT JOIN [telecom].[PhoneNumber] AS [n]
        ON [n].[Id] = [t].[PhoneNumberId]
    LEFT JOIN [telecom].[ClientPhoneNumber] AS [cn]
        ON [cn].[PhoneNumberId] = [n].[Id]
            AND [cn].[ClientId] = [c].[Id]
    LEFT JOIN [telecom].[TelecomUser] AS [tu]
        ON [tu].[ClientPhoneNumberId] = [cn].[Id]
    LEFT JOIN [people].[Person] AS [cp]
        ON [cp].Id = [cn].[PhoneCompanyId]
    LEFT JOIN [people].[Person] AS [p2]
        ON [p2].[Id] = [tu].[PersonId]
    LEFT JOIN [people].[Person] AS [mg]
        ON [mg].[Id] = [p2].[SuperiorId]
    LEFT JOIN [people].[Departament] AS [dp]
        ON [dp].[Id] = [cn].[DepartamentId]
    LEFT JOIN [accounting].[CostCenter] AS [cc]
        ON [cc].[Id] = [dp].[CostCenterId]
    LEFT JOIN [telecom].[TelecomUserCostCenter] AS [tuc]
        ON [tuc].[TelecomUserId] = [tu].[Id]
    LEFT JOIN [accounting].[CostCenter] AS [tcc]
        ON [tcc].[Id] = [tuc].[CostCenterId]
    LEFT JOIN [telecom].[PhoneNumber] AS [n1]
        ON [n1].[Id] = [t].[CalledPhoneNumberId]
    WHERE (
            [t].[CallDate] BETWEEN [tu].[InitialDate]
                AND (
                        CASE 
                            WHEN [tu].[EndDate] IS NULL
                                THEN getdate()
                            ELSE [tu].[EndDate]
                            END
                        )
            OR [t].[CallDate] IS NULL
            )
        AND [ShowToUser] = 1
        AND [c].[Id] = @clientId
        AND [p2].[SuperiorId] = @superiorId
        AND [cn].[PhoneCompanyId] = ISNULL(@phoneCompanyId, [cn].[PhoneCompanyId])
        AND (
            ISNULL([cn].[DepartamentId], 0) = ISNULL(@departamentId, 0)
            OR [cn].[DepartamentId] = ISNULL(@departamentId, [cn].[DepartamentId])
            )
        AND [a].[ReferenceMonth] BETWEEN @referenceMonthInitial
            AND @referenceMonthEnd
    ) AS tb
GROUP BY [ClientId],
    [PersonId],
    [FullName],
    [Manager],
    [ClientPhoneNumberId],
    [PhoneNumber],
    [Departament],
    [CostCenter],
    [CostCenterCode],
    [TelecomUserInitialDate],
    [TelecomUserEndDate],
    [SuperiorId],
    [TelecomUserId],
    [PhoneCompanyId],
    [PhoneCompanyName],
    Meta,
    ServicesValue,
    RegisteredArea
3

Good work updating statistics as well as checking indexes.

With that monster, you need to simplify and reduce the load and complexity as early as possible.

  • Anything in your WHERE clause that can be put into a JOIN safely should be, unless you measure a performance degradation.
    • such as an equality to a parameter!
    • this lets SQL eliminate rows as early as possible
  • Create #temp tables for subsets of those tables - a few joined together at a time

    • And put useful unique clustered indexes on those #temp tables!
    • Apply your filtering as early as possible, getting only the rows you need
    • Get ONLY the columns you need, plus enough for uniqueness (to prevent bad data)

    • As an example, CREATE TABLE #cnAndSubset (cols you need), and then INSERT into it every row you need from cn and every table that joins ONLY on cn. Index that #temp table and then replace the existing many joins in the query with one join to the #temp table. Repeat with reasonable sets until performance levels off.

  • Figure out WHY you have the DISTINCT and that massive GROUP BY

    • Most common cause: joins that fail to isolate only the rows required correctly
    • on that GROUP BY - if Manager is ALWAYS the same for a given PersonId, and you're already grouping on PersonID, take the MAX(Manager) or MIN(Manager), don't group by it!
1

I'd do all the already-mentioned tricks in the other answers, but also clean up that inner query to the following. (there was some crazy filter logic overcomplications)

    ...
    FROM [telecom].[CallDetailRecord] AS [t]
    JOIN [telecom].[TelecomBill] AS [a] ON [a].[Id] = [t].[TelecomBillId]
        AND [a].[ReferenceMonth] BETWEEN @referenceMonthInitial
            AND @referenceMonthEnd
    JOIN [telecom].[TelecomBillAccount] AS [tba] ON [tba].[Id] = [a].[TelecomBillAccountId]
    JOIN [telecom].[TelecomBillAccountContract] AS [tbacon] ON [tbacon].[TelecomBillAccountId] = [tba].[Id]
    JOIN [doc].[PersonContract] AS [pcontr] ON [pcontr].[Id] = [tbacon].[ContractId]
    JOIN [people].[Person] AS [p] ON [p].[Id] = [pcontr].[ContractorId]
    JOIN [customer].[Client] AS [c] ON [c].[PersonId] = [p].[Id]
        AND [c].[Id] = @clientId
    JOIN [telecom].[PhoneNumber] AS [n] ON [n].[Id] = [t].[PhoneNumberId]
    JOIN [telecom].[ClientPhoneNumber] AS [cn] ON [cn].[PhoneNumberId] = [n].[Id]
        AND [cn].[ClientId] = [c].[Id]
    JOIN [telecom].[TelecomUser] AS [tu] ON [tu].[ClientPhoneNumberId] = [cn].[Id]
    JOIN [people].[Person] AS [p2] ON [p2].[Id] = [tu].[PersonId]
        AND [p2].[SuperiorId] = @superiorId
    LEFT JOIN [people].[Person] AS [cp]
        ON [cp].Id = [cn].[PhoneCompanyId]
    LEFT JOIN [people].[Person] AS [mg]
        ON [mg].[Id] = [p2].[SuperiorId]
    LEFT JOIN [people].[Departament] AS [dp]
        ON [dp].[Id] = [cn].[DepartamentId]
    LEFT JOIN [accounting].[CostCenter] AS [cc]
        ON [cc].[Id] = [dp].[CostCenterId]
    LEFT JOIN [telecom].[TelecomUserCostCenter] AS [tuc]
        ON [tuc].[TelecomUserId] = [tu].[Id]
    LEFT JOIN [accounting].[CostCenter] AS [tcc]
        ON [tcc].[Id] = [tuc].[CostCenterId]
    LEFT JOIN [telecom].[PhoneNumber] AS [n1]
        ON [n1].[Id] = [t].[CalledPhoneNumberId]
    WHERE (
            [t].[CallDate] BETWEEN [tu].[InitialDate]
                AND ISNULL([tu].[EndDate], getdate())
            OR [t].[CallDate] IS NULL
            )
        AND [ShowToUser] = 1
        AND (@phoneCompanyId IS NULL OR [cn].[PhoneCompanyId] = @phoneCompanyId)
        AND (@departamentId IS NULL OR [cn].[DepartamentId] = @departamentId)
    ) AS tb
...
  • Unfortunately rewriting the query, slowed it down though I believe your idea makes more sense. This server is in a Standard 2016 version, I thought about using the In-Memory OLTP feature, is this a good idea? – Rafaells Feb 19 '18 at 20:27
  • 1
    From the MS webpage on IM OLTP: "In-Memory OLTP can significantly improve the performance of transaction processing, data ingestion/load, and transient data scenarios." You are doing reporting (aka. just a SELECT) so I don't think it will help in this case. – Oreo Feb 20 '18 at 11:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.