May 13, 2009 at 10:37 am
Hi All,
I have a issue where the below View sometime takes too much time to give the resluts and many times eats Memory .
Plz find the Code attached.
I am guessing it should be the issue with PIVOT being used.
Can some one help me Tune this Query.
Many Thanks,
Gagan
May 13, 2009 at 11:12 am
Hi All,
I have the Actual Execution Plan attached. Hope this helps to Analyse/understand.
Any tunning help Appericiated
Best Regards,
Gagan
May 13, 2009 at 12:38 pm
Your suspicion is right. The PIVOT does seem to be the main problematic piece here. Pivots are slow, and CPU intensive when pivoting large amounts of data (in your case it's 50,000+ rows I believe). My only recommendation is filter the data as much as you can prior to pivoting the data. Filtering out what you do not need ahead of time will also reduce the expensive hash joins.
Also, it looks like you have a few index scans. Fixing these may help reduce the amount of data being pivoted. You may want to consider some covering indexes.
I hope this gets you on the right track.
Also, have you considered using the old school way of pivoting (with CASE statement) to see if that makes a difference?
A.J.
DBA with an attitude
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply