Select query inside procedure taking long time, clustered indexes doing scans, need suggestion

I have a procedure, which is running very slow, and is being timed out without returning any records. [Reports] Table is already generated by another SP, which creates indexes on this underlying table ([Reports]) and this SP just reads data from this [Reports] table.

Approx number of records 150K and is taking more than 15 minutes to load this data.

End user needs "Select All" for all filters i.e. they need data for all filters available on report.

I have added current execution plan for slow procedure and underlying table structure:

What could be the reason for slow performance?

Things I have tried:

  • --Local Parameters are already in place (to avoid parameter sniffing) --Updated statics --Tried to have column store index on all VARCHAR(MAX) columns --SET ARITHABORT ON --OPTION (OPTIMIZE FOR UNKNOWN) --Tried to create full text search column but couldn't do due to underlying table structure (no unique single non-nullable column index is available)

But there is no improvement in performance.

Please let me know if additional information is required to answer this question and give suggestions


avatar image By biandsqluser 1 asked Dec 05, 2016 at 11:07 AM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

I get a 404 error on the link. Also, local parameters might seem like a good idea to avoid parameter sniffing, but it sounds like my solution to "the kitchen sink" - which uses dynamic SQL and OPTION (RECOMPILE) - might be more appropriate.

avatar image By Aaron Bertrand ♦ 1.7k answered Jan 08, 2017 at 05:03 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: Dec 05, 2016 at 11:07 AM

Seen: 99 times

Last Updated: Jan 09, 2017 at 03:06 AM