I am extracting data from two large tables each approx 50M rows. Distribution of data on the pages is not the best but could be termed less than satisfactory.
While we would like to do the maintenance on the database tables and indexes and distribution, we have deferred it to get most uptime of the systems.
I am now facing a problem with a proc which is performing poorly. Performance went down from a duration of under 20-30 seconds to over 15 minutes.
I executed the sql statements from the proc and am able to get the results in less than 15-30 seconds.
But when I execute the proc, it takes 15 minutes.
I recompiled and recreated the proc also to have a new and optimized query plan, but still the performance is over 15 minutes. Even the stats have been redone.
The proc uses union all as well in the logic (just to prevent any duplicates), though ther chances of getting duplicate rows are slim to none.
What could be missing? Any thoughts?
Thanks in advance,
Check out the forums at: