Select query inside procedure taking long time, clustered indexes doing scans, need suggestion
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)
–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
But there is no improvement in performance.
Please let me know if additional information is required to answer this question and give suggestions