This query is taking over 2 minutes to run. Please help

Gary Singh 2018-04-19 08:02:57

This query is taking over 2 minutes to run. Please see what can we optimize.
Thanks
Gary

Hugo Kornelis 2018-04-19 14:53:41
What you posted is not a single query, but a whole batch of over 40 statements. Some of them run fast, several take between 5 and 15 seconds, and at the end is a single huge statement that does left joins to what seems like every table you could find in your database, in some cases in subqueries with grouping or distinct, and then returns 200K rows to the client. That last statement takes ~40 seconds.

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.

SDyckes2 2018-04-19 19:46:17
Taking a quick look at the plan provided, I see several "Low-Hanging Fruits". The SQL Optimizer has provided several potential Missing Indexes. Additionally, there are also a few Key Lookups that could be addressed. The next set of opportunities are the Clustered Index Scans and Index Scans. So far all of these tuning opportunities are based on doing some Index Analysis. Our Index Analysis part of Plan Explorer (PE), it can help you quickly build theoretical indexes and show you the potential impact by the increase of the Hit Percentage. Aaron Bertrand has a great blog post that can help you use and understand the Index Analysis tool and the PE application.

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:

  1. No covering index is available on the tables involved in the join, union or aggregation operations.
  2. 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.