Custom Report Takes FOREVER to load - Please Help?

[Sarah_MacG]

Hey guys,

I'm not a reporting pro what so ever and so I'm turning to the community for some help!  I have this custom report that takes roughly 30 minutes to load. Was wondering if I could get some help with making it more efficient.  File attached..

[rinks]

1. Just a few observations based on the code, but you can filter the tblQuickFixEngineering before joining: 

LEFT JOIN (
SELECT AssetID, MAX(Convert(date, InstalledOn)) AS lastPatchDate
FROM tblQuickFixEngineering
GROUP BY AssetID
) AS patch ON patch.AssetID = tblAssets.AssetID

Then refer to patch.lastPatchDate in the SELECT and ORDERBY.

2. To hit indexes faster, change LIKE '%forti%' to: softwareName LIKE 'forti%' OR softwareName LIKE '%forticlient%'
or pre-filter tblSoftwareUni by name first and then join.

3. There's also a duplicate calculation. You can format into a CTE or simplify it:
-- Remove from GROUP BY:
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 / 24))) + ...

Instead, alias this once in SELECT and remove it from the GROUP BY:
, tblAssets.Uptime AS RawUptime -- Group by this

4. Change FULL JOIN to LEFT JOIN unless you need unmatched users from both sides, LEFT JOIN is more appropriate:
LEFT JOIN tblADusers ON tblADusers.Username = tblAssetUserRelations.Username

5. Add a date filter if you're only interested in assets seen recently:
WHERE tblAssets.Lastseen > DATEADD(month, -3, GETDATE())

Summary:

Issue Recommendation

Slow MAX() and date conversion	Pre-aggregate tblQuickFixEngineering in a subquery
Multiple LIKE %...% filters	Use more specific matches or indexes
Duplicate uptime calculation	Move to CTE or simplify
FULL JOIN	Change to LEFT JOIN
No data limit	Add time-based filters if appropriate

[Sarah_MacG]

Thank you!  I will try these reccomendations and let you know.  🙂