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

biandsqluser 2016-12-05 11:07:17

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:

https://drive.google.com/open?id=0BxYEMI40aU_aRjZaVzJyMXJfb0k

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

Thanks!!!

Aaron Bertrand 2017-01-08 17:03:51
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.