This query is taking over 2 minutes to run. Please help
If you want to optimmze, I would start by thinking about which rows are actually needed at the client. Filter the data as soon as possible so that all later steps in the procedure run with less data.
Oh, and I would explicitly create the temp tables with ALL required columns, instead of the repeated ALTER statements where you add a column every time.
That's all I have time for at the moment.
I would explore the missing index, CREATE NONCLUSTERED INDEX [Name of Missing Index, sysname,]
ON [dbo].[timInvtLot] ([Status],[LotNo])
INCLUDE ([InvtLotKey],[ItemKey],[WhseKey]). It will impact the queries in multiple places, providing a bigger bang for the buck.
If you would like to address the Key Lookups, you can read through my answer that details removing Key Lookups in this answer.
The longest running statement of the query is taking 41 seconds to complete. This statement executes 23 Hash Matches, 16 Clustered Index Scans, and 5 Table Scans. It is a complex piece of code that is doing a lot of aggregation. The Hash Matches are one of the more expensive operations due to the creation of a Hash table and are utilized by SQL for 2 reasons:
- No covering index is available on the tables involved in the join, union or aggregation operations.
- One large table is being joined against a much small table, Hash Match sometimes proves to be very efficient in these cases.
In the cases #1 covers, creating the proper index(s) can eliminate this costly operation.