Question :
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
Answer :
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
WHEREclause that can be put into aJOINsafely 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 thenINSERTinto it every row you need fromcnand every table that joins ONLY oncn. 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
DISTINCTand that massiveGROUP 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 theMAX(Manager)orMIN(Manager), don’t group by it!
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
...