SELECT with many LEFT JOINs runs very slowly

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
WH

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!

StackExchange Database Administrators Q#198233, answer score: 4