I'm tasked with making this query run under 4 seconds

avatar image By jrb2971 1 asked Nov 08 at 01:39 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

2 answers: sort voted first

Without only an anonymized plan, it is difficult to help with specifics.

The optimizer has pointed out there is a potential missing index on Object2 (right click on the SELECT in the plan and choose the Missing Index Details... option).

Object5.Index5 is being read twice, with the same row count for both on the read, then the same sort is being done. I recommend investigating methods to only do the work on Object5 one time. This may include using a temp table and/or rewriting the query and joins.

There is also a Table Valued Function being used on Object8, I would investigate removing the use of the TVF. It is the highest CPU cost in your plan.

Providing more details of the query and options you have tried can provide a better view and allow us to provide more detailed help.

avatar image By SDyckes2 81 answered Nov 08 at 03:12 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

I replaced the table valued functions with CTEs. The function just returns a table from a comma delimited string (params). This is SQL 2012. My query went from 11 seconds to 8 seconds. I'm not sure putting the function in CTEs made it only called once instead of twice.

avatar image By jrb2971 1 answered Nov 08 at 05:48 PM
more ▼
(comments are locked)
avatar image jrb2971 Nov 08 at 05:55 PM

I added the missing NC index. Also the object5.index5 read is two separate joins to the same table but each join adds another key column lookup which makes them different. I don't know how to get around that.

avatar image SDyckes2 Nov 08 at 06:10 PM

You can also look into the Inner Joins, can they be joined by an additional column or possible foreign key? There is a lot of residual IO occurring in each of the Clustered Index Scans, one was reading 1.2million rows to return 156,000.

There is also a Convert Issue highlighted in a CASE statement with the SELECT. If you are able to remove the convert, you may gain some performance.

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.