August 14, 2017 at 11:11 pm
I've been tasked with performance tuning the underlying query. I have attached all the necessary details but if there is anything that seems missing let me know and I can add it as well. As per the query plan I noticed more than a couple of occurrences of tempdb spills but I am kind of lost with this plan and can't figure out a way to tune this query. I tried adding a few indexes but that did not work so great. It helped reduce the execution time a bit but not to a huge extent just a few minutes. Any help would be much appreciated.
EXEC report.ForeignBidding @StartDate = '12/1/2015', @EndDate = '2/18/2016', @Type = -1;
August 15, 2017 at 3:38 am
ffarouqi - Monday, August 14, 2017 11:11 PMI've been tasked with performance tuning the underlying query. I have attached all the necessary details but if there is anything that seems missing let me know and I can add it as well. As per the query plan I noticed more than a couple of occurrences of tempdb spills but I am kind of lost with this plan and can't figure out a way to tune this query. I tried adding a few indexes but that did not work so great. It helped reduce the execution time a bit but not to a huge extent just a few minutes. Any help would be much appreciated.
EXEC report.ForeignBidding @StartDate = '12/1/2015', @EndDate = '2/18/2016', @Type = -1;
You should get rid of the multi statement table valued function, it is a killer for the performance. Try either to rewrite the query without it or change it to a inline table valued function, once that is done, then you can start looking at other things.
😎
August 15, 2017 at 7:17 am
Eirikur Eiriksson - Tuesday, August 15, 2017 3:38 AMffarouqi - Monday, August 14, 2017 11:11 PMI've been tasked with performance tuning the underlying query. I have attached all the necessary details but if there is anything that seems missing let me know and I can add it as well. As per the query plan I noticed more than a couple of occurrences of tempdb spills but I am kind of lost with this plan and can't figure out a way to tune this query. I tried adding a few indexes but that did not work so great. It helped reduce the execution time a bit but not to a huge extent just a few minutes. Any help would be much appreciated.
EXEC report.ForeignBidding @StartDate = '12/1/2015', @EndDate = '2/18/2016', @Type = -1;
You should get rid of the multi statement table valued function, it is a killer for the performance. Try either to rewrite the query without it or change it to a inline table valued function, once that is done, then you can start looking at other things.
😎
Other things includes optimiser timeout, which isn't surprising given the complexity of the query. However, several tables are read numerous times. The table DealsToEmployees is read at least four times. CacheContacts is read at least four times. Using base tables instead of views will probably eliminate this duplication.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 15, 2017 at 7:31 am
This thing is a beast. May I ask why you were the one tasked with tuning it? What are your qualifications - what makes your company think you will be successful at this endeavor?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 15, 2017 at 8:01 am
TheSQLGuru - Tuesday, August 15, 2017 7:31 AMThis thing is a beast. May I ask why you were the one tasked with tuning it? What are your qualifications - what makes your company think you will be successful at this endeavor?
Yeah I know it is a beast. I ask that question to myself as well but unfortunately I don't have an answer for this. It was my manager's call and I had to abide by the same irrespective of my qualifications. I would also request you to please refrain from certain questions that you ask about qualifications and stuff. I believe forum is for people to come and ask questions and see if someone can help them out if possible after trying a few things in order to resolve the problem. Please understand that not everyone is expert like you I guess to be qualified enough. I would suggest you to be quiet rather than make noise if you don't want to help any further. If you think you have the qualification then please go ahead and propose a solution...don't just ask unnecessary questions. If things would have been under my control I wouldn't have accepted to do this task either and didn't even bothered to ask question in the forum.
August 15, 2017 at 9:11 am
ffarouqi - Tuesday, August 15, 2017 8:01 AMTheSQLGuru - Tuesday, August 15, 2017 7:31 AMThis thing is a beast. May I ask why you were the one tasked with tuning it? What are your qualifications - what makes your company think you will be successful at this endeavor?Yeah I know it is a beast. I ask that question to myself as well but unfortunately I don't have an answer for this. It was my manager's call and I had to abide by the same irrespective of my qualifications. I would also request you to please refrain from certain questions that you ask about qualifications and stuff. I believe forum is for people to come and ask questions and see if someone can help them out if possible after trying a few things in order to resolve the problem. Please understand that not everyone is expert like you I guess to be qualified enough. I would suggest you to be quiet rather than make noise if you don't want to help any further. If you think you have the qualification then please go ahead and propose a solution...don't just ask unnecessary questions. If things would have been under my control I wouldn't have accepted to do this task either and didn't even bothered to ask question in the forum.
You are not understanding the reason for the question, and it certainly was not unnecessary. One reason was hopefully that you had an ability (or there was a misunderstanding somehow) to get the company to see that you were indeed not qualified if you were not. I have seen this at clients repeatedly over the years - and spent a lot of time helping to clean up the messes created when inappropriate staff were tasked with jobs they were simply untrained to be successful at and those staff not stating that the job was beyond them.
The other reason is that I have spent a LOT of time answering questions on this and other forums and with clients and have come to realize that often my answer isn't comprehendable by the OP/staff because he/she doesn't have the training or skill set to make use of the assistance (other than copy/pasting code, which doesn't teach much) - this goes directly to your "expert like you" statement. The flip side is diving into simplistic stuff for someone with 10+ years of experience (that is not the "Groundhog Day" world that so many techies live in), resulting in a different form of unhelpfulness, hurting someone's feelings, etc.
I do thank you for giving me a point of data to indicate that trying to better understand the OP in such a scenario is far worse than what I and others often do in such cases, which is to simply recommend getting a performance tuning consultant on board to assist.
I am sorry for the misunderstanding, but having said that given how things have unfortunately transpired I think it best to bow out. There are plenty of others on SSC.com that can help, and you have already been given good advice. I'm sure others will point out the myriad additional findings I have identified/things to investigate and hopefully you can spiffy them up and have a fine solution.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 15, 2017 at 10:18 am
TheSQLGuru - Tuesday, August 15, 2017 9:11 AMffarouqi - Tuesday, August 15, 2017 8:01 AMTheSQLGuru - Tuesday, August 15, 2017 7:31 AMThis thing is a beast. May I ask why you were the one tasked with tuning it? What are your qualifications - what makes your company think you will be successful at this endeavor?Yeah I know it is a beast. I ask that question to myself as well but unfortunately I don't have an answer for this. It was my manager's call and I had to abide by the same irrespective of my qualifications. I would also request you to please refrain from certain questions that you ask about qualifications and stuff. I believe forum is for people to come and ask questions and see if someone can help them out if possible after trying a few things in order to resolve the problem. Please understand that not everyone is expert like you I guess to be qualified enough. I would suggest you to be quiet rather than make noise if you don't want to help any further. If you think you have the qualification then please go ahead and propose a solution...don't just ask unnecessary questions. If things would have been under my control I wouldn't have accepted to do this task either and didn't even bothered to ask question in the forum.
You are not understanding the reason for the question, and it certainly was not unnecessary. One reason was hopefully that you had an ability (or there was a misunderstanding somehow) to get the company to see that you were indeed not qualified if you were not. I have seen this at clients repeatedly over the years - and spent a lot of time helping to clean up the messes created when inappropriate staff were tasked with jobs they were simply untrained to be successful at and those staff not stating that the job was beyond them.
The other reason is that I have spent a LOT of time answering questions on this and other forums and with clients and have come to realize that often my answer isn't comprehendable by the OP/staff because he/she doesn't have the training or skill set to make use of the assistance (other than copy/pasting code, which doesn't teach much) - this goes directly to your "expert like you" statement. The flip side is diving into simplistic stuff for someone with 10+ years of experience (that is not the "Groundhog Day" world that so many techies live in), resulting in a different form of unhelpfulness, hurting someone's feelings, etc.
I do thank you for giving me a point of data to indicate that trying to better understand the OP in such a scenario is far worse than what I and others often do in such cases, which is to simply recommend getting a performance tuning consultant on board to assist.
I am sorry for the misunderstanding, but having said that given how things have unfortunately transpired I think it best to bow out. There are plenty of others on SSC.com that can help, and you have already been given good advice. I'm sure others will point out the myriad additional findings I have identified/things to investigate and hopefully you can spiffy them up and have a fine solution.
Cool...you are just a man of words I wasn't expecting any answers from you either. I've seen in the past how you respond to OP's request, so it is not uncommon for me to see how you blatantly question someone else's qualification. Nevertheless, there are helpful souls around here that can point me in right direction and yes of course the people who have commented above have actually given me something that I can speak about at least if not completely resolve the problem. You can't be an expert in each and every aspect of SQL...I might be good in something else but not that great in writing T-SQL, but it doesn't mean that I am not qualified enough for this task or can't learn by trying to resolve a problem and asking for help in the forum...at least I am trying my best. If my manager hires a consultant the first question he would ask me is why am I paying your for. I will ask him to reach out to you instead and get that answer because you keep pointing all the OP's to use performance tuning consultants. In case if we wanted to hire a performance tuning expert then there was no point in coming over here and asking questions, dealing with people like yourself and hoping for an answer.
Anyways...long story short. Have a great rest of the day and may God bless you.
August 15, 2017 at 11:51 am
Very busy at the moment so I don't have time to analyse this in any more details but here are just few thoughts:
😎
Removing the multi statement table valued function should be priority number one, I normally see massive improvements like going from hours down to seconds in execution time, especially where the query plans are more complex.
If you cannot remove that function (I've always been able to rewrite the code to eliminate this kind of functions), make certain that the tempdb is properly configured and then add resources as needed, i.e. SSDs, CPUs RAM etc because that's what you are going to need.
The negative effect of those functions is so great that it does not serve any purpose trying to do any other optimisation until they have been removed.You won't find much better resources than Kevin, Chris and the other top posters on this forum so I would suggest you pay full attention to what they have to say.
August 15, 2017 at 11:52 am
With these type of query you can sometimes get a lot of success from breaking it up into several queries inserting the data into temporary tables, adding indexes to the temporary tables and then querying the temporary tables to get the results. You could start off by inserting the results of he function into a temporary table:IF OBJECT_ID('temdb..#ReportDealCore','U') IS NOT NULL
DROP TABLE #ReportDealCore
SELECT FDC.DealID,
FDC.StageID,
FDC.PlatformID
INTO #ReportDealCore
FROM report.fnReportDealCore(@StartDate, @EndDate, @Type) AS FDC
Then query the able #ReportDealCore instead of calling the function in the query.
Then just select several tables from the query join them together and insert the results into another temporary table, then build up the whole query from joins to the temporary tables,
August 15, 2017 at 12:49 pm
Eirikur Eiriksson - Tuesday, August 15, 2017 11:51 AMVery busy at the moment so I don't have time to analyse this in any more details but here are just few thoughts:
😎
Removing the multi statement table valued function should be priority number one, I normally see massive improvements like going from hours down to seconds in execution time, especially where the query plans are more complex.If you cannot remove that function (I've always been able to rewrite the code to eliminate this kind of functions), make certain that the tempdb is properly configured and then add resources as needed, i.e. SSDs, CPUs RAM etc because that's what you are going to need.
The negative effect of those functions is so great that it does not serve any purpose trying to do any other optimisation until they have been removed.You won't find much better resources than Kevin, Chris and the other top posters on this forum so I would suggest you pay full attention to what they have to say.
I completely agree with you...those multi statement table valued function is a bottleneck. I will definitely ask the development team to review the same.
August 15, 2017 at 12:50 pm
Jonathan AC Roberts - Tuesday, August 15, 2017 11:52 AMWith these type of query you can sometimes get a lot of success from breaking it up into several queries inserting the data into temporary tables, adding indexes to the temporary tables and then querying the temporary tables to get the results. You could start off by inserting the results of he function into a temporary table:IF OBJECT_ID('temdb..#ReportDealCore','U') IS NOT NULL
DROP TABLE #ReportDealCoreSELECT FDC.DealID,
FDC.StageID,
FDC.PlatformID
INTO #ReportDealCore
FROM report.fnReportDealCore(@StartDate, @EndDate, @Type) AS FDC
Then query the able #ReportDealCore instead of calling the function in the query.Then just select several tables from the query join them together and insert the results into another temporary table, then build up the whole query from joins to the temporary tables,
I kind of really like your suggestion. I definitely feel it should solve most of the execution problems. I will definitely reach out to my manager with this extremely useful suggestion.
August 15, 2017 at 1:36 pm
Jonathan AC Roberts - Tuesday, August 15, 2017 11:52 AMWith these type of query you can sometimes get a lot of success from breaking it up into several queries inserting the data into temporary tables, adding indexes to the temporary tables and then querying the temporary tables to get the results. You could start off by inserting the results of he function into a temporary table:IF OBJECT_ID('temdb..#ReportDealCore','U') IS NOT NULL
DROP TABLE #ReportDealCoreSELECT FDC.DealID,
FDC.StageID,
FDC.PlatformID
INTO #ReportDealCore
FROM report.fnReportDealCore(@StartDate, @EndDate, @Type) AS FDC
Then query the able #ReportDealCore instead of calling the function in the query.Then just select several tables from the query join them together and insert the results into another temporary table, then build up the whole query from joins to the temporary tables,
Can you suggest what index would benefit here on the temp table to have it fetch quickly when doing a join. Can we do something on the joining columns only or do we need to include other columns as well.
August 15, 2017 at 1:52 pm
ffarouqi - Tuesday, August 15, 2017 1:36 PMCan you suggest what index would benefit here on the temp table to have it fetch quickly when doing a join. Can we do something on the joining columns only or do we need to include other columns as well.
Can you provide a list with the table name and how many rows are in each table?
August 15, 2017 at 3:37 pm
Jonathan AC Roberts - Tuesday, August 15, 2017 1:52 PMffarouqi - Tuesday, August 15, 2017 1:36 PMCan you suggest what index would benefit here on the temp table to have it fetch quickly when doing a join. Can we do something on the joining columns only or do we need to include other columns as well.Can you provide a list with the table name and how many rows are in each table?
I have attached the list of tables with the rows in each. By the way I was asking about the temp table as well. Based on the message the table is holding 2181 records in temp table from that function.
August 16, 2017 at 1:44 am
ffarouqi - Tuesday, August 15, 2017 3:37 PMJonathan AC Roberts - Tuesday, August 15, 2017 1:52 PMffarouqi - Tuesday, August 15, 2017 1:36 PMCan you suggest what index would benefit here on the temp table to have it fetch quickly when doing a join. Can we do something on the joining columns only or do we need to include other columns as well.Can you provide a list with the table name and how many rows are in each table?
I have attached the list of tables with the rows in each. By the way I was asking about the temp table as well. Based on the message the table is holding 2181 records in temp table from that function.
Here's what I suggest you do:
Run the results of the function into a temp table and reference the temp table in your query instead of the function, as already mentioned. Save and post here an actual execution plan from the modified query.
Post up the definitions of all of the views referenced by the query.
Cheers
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply