February 24, 2010 at 3:39 am
Hello All,
I am facing issue for Slow performance of one SP, I tried different ways to improve performance like Index, update statistics , rebuild index , code optimization etc. but still this SP takes more than 2 min to complete the execution. I hope some can help me on this issue.
IN this SP I am showing last 6 months history for data month by month, the history table is having more than 10 L records out of which I need to get only data for valid last 6 months
data added before 1 year can also be part of current month data in case whole process is not completed.
when i looked at the execution plan i found the attached execution plan taking n more time, the whole SP takes more than 2 min time and this code as well takes more 50 % in whole execution plan.
I have attached the Execution plan which takes more time.
Can anyone help me on this issue?
Thank you in advance.
Yatish
February 24, 2010 at 4:49 am
Can you provide DDL and sample data ?
I have one or two ideas i would like to try.
February 24, 2010 at 5:31 am
just fyi i think this is performing badly due to a bug in 2005 ive documented here
What you need to do is use top(1) within the cross apply with than filtering on row_number.
February 24, 2010 at 7:57 am
Could you move some of the filtering into the Derived Table instead of outside the derived table. For example, couldn't this, OH.TeamIsLost = 'N', be int eh derived table to reduce the # rows retrieved there?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 24, 2010 at 10:25 pm
Hello,
Thank You for your suggestions, it has really help me to improve the performance, thank you very much for your help
I did the changes and removed the Row_number() and used sub query with max and now performance is improved much better than it was previous. I did tried the Top 1 but id didn't produced the out put I required.
with the modification for using the filter condition to the temp table also help to improve.
I have attached the latest Execution plan which is now taking over 1 min to complete.
I request to suggest me with current code how i can improve more.
Thank you in advance
Yatish
February 25, 2010 at 7:45 am
I notice that the new execution plan parallelizes and many times that is actually slower than serial execution. You can test by adding the OPTION(Maxdop 1) hint to your query.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 25, 2010 at 9:50 pm
Hello Jack,
Thanks for your help, I tried adding the OPTION(Maxdop 1) hint to query. but it has not helped to improve the performance can suggest me any other ways to improve the performance?
Thank you
yatish
February 26, 2010 at 3:14 am
Hello,
I have made few changes in the Query that i would like to share with you, It has help me to get few more improvements.
I have attached the new execution plan, Please let me know your comments suggestion to improve the performance.
1) History is the execution plan for the query which collect the history data first
2) I have created one index on this temp table
3) the history data is used to find monthly data in the last
Thank You in Advance
regards
yatish
March 2, 2010 at 12:57 am
can anyone please help me on this issue.
Thank you
March 2, 2010 at 1:23 am
Can you post the code for all the sql involved (including functions etc)
March 2, 2010 at 2:58 am
Hello,
Please find the attachments.
thank you
March 3, 2010 at 11:52 pm
hell Jack
can you tell me by adding the OPTION(Maxdop 1) hint to query will it cost performance when multiple users accessing same data at same time from different location.
Thank you in advance
March 4, 2010 at 5:58 am
No the option MAXDOP 1 hint will not have any cost when a query is run simultaneously by multiple users. This hint has nothing to do with concurrency, it just tells the Optimizer to not consider parallelism. In most cases, parallelism actually has a higher cost than using a serial plan. There are exceptions to this, especially in data warehouse situations, but in most cases a serial plan is less expensive than a parallel plan.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 5, 2010 at 1:48 am
Thanks jack for your help.
I did tried some changes to improve the performance and has able to bring down the time.
Still I need help from this forum to help me to improve the performance more.
i am attaching the latest execution plan with this reply, I have also one question regarding the index i have created on temp table is that correct or do i need to modify it, also the index takes more time create than the block takes time to fill data in temp table is this normal?
Please help me on these Performance issues. Thank you for your help in advance.
Yatish
March 5, 2010 at 1:59 am
I forgot to ask question about Hash match Join, you can check that in the execution plan it is taking time. how to improve performance for these Hash match joins?
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply