Query taking around 25-30 mins while fetching data from not to big a table

SQLDB 2016-05-12 10:02:09

Hello Experts,

For attached plan, just to fetch a single record the SP takes around 2-3 minuts from tables in range of few thousands to 0.5 million records.

I tried to run the update stats with full scan. Still i see the estimated and actual varying in large number.

Please guide how can we improve here as i have removed most of lookups and scans but yet, its expected to complete in 5-10 secs.

Kindly suggest

SQLkiwi 2016-05-20 16:27:33
The most problematic part of the plan is:

Actual plan fragment

That is the actual view. Compare it to the estimates:

Estimated plan fragment

The common factor is the single-row estimate for the table variables produced by your string splitting function. I mentioned this in my answers to your previous questions. You will get better results if you split the strings in a separate step and store the results in temporary tables (not table variables).

You also need to look at the indexing on the table with the Clustered Index Scan. You seem to be missing an index on (Company, OperatingUnitDescription,Goby).

The other advice I offered before still stands. Your query is too large to be manageable for humans or the optimizer. Break it up.

SQLDB 2016-05-26 09:35:32
Thank you for the helpful suggestions. I got this working.