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

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

avatar image By Gary Singh 0 asked Apr 19 at 08:02 AM
more ▼
(comments are locked)
10|10000 characters needed characters left

2 answers: sort voted first

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.

avatar image By Hugo Kornelis 271 answered Apr 19 at 02:53 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

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.

avatar image By SDyckes2 96 answered Apr 19 at 07:46 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question



asked: Apr 19 at 08:02 AM

Seen: 27 times

Last Updated: Apr 19 at 07:46 PM