Performance Tuning a query running since more than 13 minutes
-
[font="Comic Sans MS"]I am desperately looking for some measure to tune this query, as this is used for reporting purpose for all the big guns. I am attaching both the query and the actual exec plan for the same. I hope someone from the forum could help me on this. Let me know if any additional details is reqd. The query timings as of now is varying...sometimes it is taking longer than usual for e.g sometimes it could totally execute within 6 minutes then 13 and then possibly 18 minutes and the trend of execution is only increasing.
Regards,
Faisal
[/font]
-
What is anyone going to do with 9 million rows being returned for a report? No one, ever, is going to read more than a small portion of that.
Start by investigating how the data is used. Is it being aggregated further or filtered in the report? Do the users look at the top 10 rows and ignore the rest?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Can you post table, index and views definitions?
My guess is that your problem is with dbo.AGGR_DAY_SUBSCRIPTION_ADDITIVE_METRICS_V. But can't be sure.
I agree with Gail, no one will look at a report with 9 million rows, they only look at the summary. Unless this is used to get a smaller recordset as it seems by the commented INTO clause.
-
Indeed, 9 million rows is an awful lot of data. Also, even assuming the report still has to exist, your execution plan is suggesting an index creation that it estimates would improve the cost of the query by a tad shy of 18%, which sure wouldn't hurt. Have you considered that option?
Here's it's suggestion:
/*
Missing Index Details from SQLActual3.sqlplan
The Query Processor estimates that implementing the following index could improve the query cost by 17.9788%.
*/
/*
USE [CDW]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[AGGR_BASE_ADDITIVE_SUBSCRIPTION_METRICS] ([CREATION_DATE_DIM_ID],[EXP_DATE_ADJUSTMENT_FLAG])
INCLUDE ([CURR_GEO_DIM_ID],[CURR_AFFILIATE_DIM_ID],[CURR_PKG_DIM_ID],[CURR_TRANS_TYPE_DIM_ID],[CURR_TRANS_SOURCE_DIM_ID],[CURR_SITE_DIM_ID],[CURR_SUB_LENGTH_DIM_ID],[CURR_CAMP_DIM_ID],[CURR_RENEWAL_TYPE_DIM_ID],[SUB_EXPIRE_MONTH_DIM_ID],[FIRST_AFFILIATE_DIM_ID],[NEW_PAID_SUBN_FLAG],[NEW_TRIAL_SUBN_FLAG],[TTP_FLAG],[RENEWAL_FLAG],[AMOUNT_USD],[ADDL_LIC_QTY],[TOTAL_QTY],[TRANSACTION_COUNT],[RETAIL_TYPE_DIM_ID],[IPT_FLAG],[MMS_GEO_DIM_ID],[EXTENDED_PROTECTION_SEGMENT_DIM_ID],[PC_USAGE_RANGE_DIM_ID],[MOBILE_USAGE_RANGE_DIM_ID],[METROAPPFLAG])
GO
*/
I don't always follow those suggestions, but it's usually worth a look-see. Also, you have a series of LEFT OUTER JOINS, the last of which has fields that participate in the WHERE clause, which will effectively turn that last LEFT OUTER JOIN into an INNER JOIN. Have you tried re-ordering the joins to include that one first? Probably should go ahead and make it an INNER JOIN anyway.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
GilaMonster (8/13/2014)
What is anyone going to do with 9 million rows being returned for a report? No one, ever, is going to read more than a small portion of that.Start by investigating how the data is used. Is it being aggregated further or filtered in the report? Do the users look at the top 10 rows and ignore the rest?
[font="Comic Sans MS"]Thanks! Gail, but this is the query that they use from the reporting tool for cube refresh, and I don't know why they need 9 million rows. However, this is all what I have to deal with, so if anything can be done in order to reduce the execution time then it would be great as I need to live with this query.
Regards,
Faisal
[/font]
-
sgmunson (8/13/2014)
Indeed, 9 million rows is an awful lot of data. Also, even assuming the report still has to exist, your execution plan is suggesting an index creation that it estimates would improve the cost of the query by a tad shy of 18%, which sure wouldn't hurt. Have you considered that option?Here's it's suggestion:
/*
Missing Index Details from SQLActual3.sqlplan
The Query Processor estimates that implementing the following index could improve the query cost by 17.9788%.
*/
/*
USE [CDW]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[AGGR_BASE_ADDITIVE_SUBSCRIPTION_METRICS] ([CREATION_DATE_DIM_ID],[EXP_DATE_ADJUSTMENT_FLAG])
INCLUDE ([CURR_GEO_DIM_ID],[CURR_AFFILIATE_DIM_ID],[CURR_PKG_DIM_ID],[CURR_TRANS_TYPE_DIM_ID],[CURR_TRANS_SOURCE_DIM_ID],[CURR_SITE_DIM_ID],[CURR_SUB_LENGTH_DIM_ID],[CURR_CAMP_DIM_ID],[CURR_RENEWAL_TYPE_DIM_ID],[SUB_EXPIRE_MONTH_DIM_ID],[FIRST_AFFILIATE_DIM_ID],[NEW_PAID_SUBN_FLAG],[NEW_TRIAL_SUBN_FLAG],[TTP_FLAG],[RENEWAL_FLAG],[AMOUNT_USD],[ADDL_LIC_QTY],[TOTAL_QTY],[TRANSACTION_COUNT],[RETAIL_TYPE_DIM_ID],[IPT_FLAG],[MMS_GEO_DIM_ID],[EXTENDED_PROTECTION_SEGMENT_DIM_ID],[PC_USAGE_RANGE_DIM_ID],[MOBILE_USAGE_RANGE_DIM_ID],[METROAPPFLAG])
GO
*/
I don't always follow those suggestions, but it's usually worth a look-see. Also, you have a series of LEFT OUTER JOINS, the last of which has fields that participate in the WHERE clause, which will effectively turn that last LEFT OUTER JOIN into an INNER JOIN. Have you tried re-ordering the joins to include that one first? Probably should go ahead and make it an INNER JOIN anyway.
[font="Comic Sans MS"]Thanks! sgmunson for looking into it. I'll try the index option but the key is quite compley and will utilize a heck lot of space and then its just 18% improvement. I am not sure whether to go with it but I'll try. Could you pls. provide the modified code with the inner joins, and I'll see if that works out in a better way.
Regards,
Faisal
[/font]
-
faisalfarouqi (8/13/2014)
GilaMonster (8/13/2014)
What is anyone going to do with 9 million rows being returned for a report? No one, ever, is going to read more than a small portion of that.Start by investigating how the data is used. Is it being aggregated further or filtered in the report? Do the users look at the top 10 rows and ignore the rest?
[font="Comic Sans MS"]Thanks! Gail, but this is the query that they use from the reporting tool for cube refresh, and I don't know why they need 9 million rows. However, this is all what I have to deal with, so if anything can be done in order to reduce the execution time then it would be great as I need to live with this query.
Regards,
Faisal
[/font]
Have you asked them if they really need 9 million rows? Have you asked them concerning the business requirements?
Start with talking to the business unit that uses the output from this report. See what they have to say and then start working on tuning the query.
Many "reporting tools" just create crap queries because nobody knew any better and just went with it. Sitting down with the consumers of the report is your first option to getting a better performing query.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
faisalfarouqi (8/13/2014)
Thanks! Gail, but this is the query that they use from the reporting tool for cube refreshIf it's a cube refresh, then it should be running scheduled out of business hours and so people shouldn't be caring how long it runs. Cube refreshes are maintenance window tasks.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Could this be part of the problem? There's a huge difference between estimated and actual number of rows.
-
[font="Comic Sans MS"]Thanks! Gail. I would be in a better position to tell you the requirement once I get some knowledge from them, and then I'll post things over here.
Regards,
Faisal
[/font]
-
Luis Cazares (8/13/2014)
Could this be part of the problem? There's a huge difference between estimated and actual number of rows.[font="Comic Sans MS"]What do you suggest then? It might be a problem from the looks of it.
Regards,
Faisal
[/font]
-
sgmunson (8/13/2014)
Indeed, 9 million rows is an awful lot of data. Also, even assuming the report still has to exist, your execution plan is suggesting an index creation that it estimates would improve the cost of the query by a tad shy of 18%, which sure wouldn't hurt. Have you considered that option?Here's it's suggestion:
/*
Missing Index Details from SQLActual3.sqlplan
The Query Processor estimates that implementing the following index could improve the query cost by 17.9788%.
*/
/*
USE [CDW]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[AGGR_BASE_ADDITIVE_SUBSCRIPTION_METRICS] ([CREATION_DATE_DIM_ID],[EXP_DATE_ADJUSTMENT_FLAG])
INCLUDE ([CURR_GEO_DIM_ID],[CURR_AFFILIATE_DIM_ID],[CURR_PKG_DIM_ID],[CURR_TRANS_TYPE_DIM_ID],[CURR_TRANS_SOURCE_DIM_ID],[CURR_SITE_DIM_ID],[CURR_SUB_LENGTH_DIM_ID],[CURR_CAMP_DIM_ID],[CURR_RENEWAL_TYPE_DIM_ID],[SUB_EXPIRE_MONTH_DIM_ID],[FIRST_AFFILIATE_DIM_ID],[NEW_PAID_SUBN_FLAG],[NEW_TRIAL_SUBN_FLAG],[TTP_FLAG],[RENEWAL_FLAG],[AMOUNT_USD],[ADDL_LIC_QTY],[TOTAL_QTY],[TRANSACTION_COUNT],[RETAIL_TYPE_DIM_ID],[IPT_FLAG],[MMS_GEO_DIM_ID],[EXTENDED_PROTECTION_SEGMENT_DIM_ID],[PC_USAGE_RANGE_DIM_ID],[MOBILE_USAGE_RANGE_DIM_ID],[METROAPPFLAG])
GO
*/
I don't always follow those suggestions, but it's usually worth a look-see. Also, you have a series of LEFT OUTER JOINS, the last of which has fields that participate in the WHERE clause, which will effectively turn that last LEFT OUTER JOIN into an INNER JOIN. Have you tried re-ordering the joins to include that one first? Probably should go ahead and make it an INNER JOIN anyway.
The SQL query looks straightforward, especially since you've explained that it's purpose is to refresh a cube, it's just joining a fact table with several dimension tables and aggregating. 20 minutes for a cube refresh is also considered normal. If it's queried many times a day, and for some operational reason it needs to return within a minute, you could implement an indexed view.
http://msdn.microsoft.com/en-us/library/ms191432(v=sql.105).aspx
But that could also open a can of worms in terms of how it would impact maintenance and any bulk loading process for the tables.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
-
faisalfarouqi (8/13/2014)
Luis Cazares (8/13/2014)
Could this be part of the problem? There's a huge difference between estimated and actual number of rows.[font="Comic Sans MS"]What do you suggest then? It might be a problem from the looks of it.
Regards,
Faisal
[/font]
I can't suggest anything until you post at least the definition of dbo.AGGR_DAY_SUBSCRIPTION_ADDITIVE_METRICS_V which I assume is a view and the tables and indexes involved.
-
Luis Cazares (8/13/2014)
faisalfarouqi (8/13/2014)
Luis Cazares (8/13/2014)
Could this be part of the problem? There's a huge difference between estimated and actual number of rows.[font="Comic Sans MS"]What do you suggest then? It might be a problem from the looks of it.
Regards,
Faisal
[/font]
I can't suggest anything until you post at least the definition of dbo.AGGR_DAY_SUBSCRIPTION_ADDITIVE_METRICS_V which I assume is a view and the tables and indexes involved.
[font="Comic Sans MS"]Thanks! Luis. I will post the details for that view by tomorrow. Fyi...when I added the missing index the query is constantly finishing up execution in 6 minutes which is not bad at all, and instead of a table scan (the extreme bottom operator from the right) is now doing an index seek. However, I'll provide you the data to understand if we can tweak in a bit more and optimize it even further.
Regards,
Faisal
[/font]
-
faisalfarouqi (8/13/2014)
[font="Comic Sans MS"]Thanks! sgmunson for looking into it. I'll try the index option but the key is quite compley and will utilize a heck lot of space and then its just 18% improvement. I am not sure whether to go with it but I'll try. Could you pls. provide the modified code with the inner joins, and I'll see if that works out in a better way.
Regards,
Faisal
[/font]
You can re-order your joins without my help... It's just a matter of changing the order in which they appear, so that the one that currently appears last, appears first, and as an INNER JOIN instead for just that one join. And as others have suggested, you might want to find out the reasoning behind the query being the way it is, with LEFT joins. Find out the why, and that usually leads to a better result in the long run...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply