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

jrb2971 2017-11-08 13:39:43

SDyckes2 2017-11-08 15:12:30
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.

jrb2971 2017-11-08 17:48:35
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.
jrb2971 2017-11-08 17:55:13
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.
SDyckes2 2017-11-08 18:10:11
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.