Query taking around 25-30 mins while fetching data from not to big a table
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
That is the actual view. Compare it to the estimates:
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.