November 6, 2013 at 7:32 am
Hi,
I require your help for optimizing/tuning the below query.
This query is a part in a stored procedure and this query alone consumes nearly 15 minutes to complete. It joins nearly 12 tables and all are indexed as mentioned below.
I used the "Execution plan" but could not understand to analyze. Also checked the Trace, there is no bottle-necks.
SELECT
r.rcc_id,
comp.company_code + '_' + comp.company_name AS 'Source Company',
RIGHT(r.rcc_code, CHARINDEX('-', REVERSE(r.rcc_code))-1 ) + '_' + r.rcc_name AS 'Source Cost Centre',
acd.activity_cat_code + '.' + acds.activity_code + '_' + acds.activity_name AS 'Source Sub-activity',
CASE cvc.cc_group
WHEN 0 THEN 'B'
WHEN 1 THEN 'S'
END AS 'Cost Type',
DestComp.company_code + '_' + DestComp.company_name AS 'Destination Company',
pc.profit_centre_code + '_' + pc.profit_centre_name AS 'Destination Profit Center',
cd.channel_code + '_' + cd.channel_name AS 'Destination Channel',
pcds.product_cat_code + '_' + pcds.product_cat_name AS 'Destination Product Category',
cust.customer_level_code + '_' + cust.customer_level_name AS 'Destination Customer',
acd.global_stream_id,
SUM(channel_cost_budg) resource_cost,
SUM(channel_cost_oge) oge_cost,
SUM(channel_cost_globe) globe_cost,
cvc.cc_group,
rcc_moge.common_cost_rcc_id AS 'Common_rcc_id',
rva.rcc_value_attr_name AS 'ValueField'
FROM
#tblChannelCost cvc
INNER JOIN profit_centre pc WITH(NOLOCK)
ON (
pc.profit_centre_id = cvc.profit_centre_id
)
INNER JOIN channel_dataset cd WITH(NOLOCK)
ON (
cd.channel_dataset_id = cvc.channel_dataset_id
)
INNER JOIN product_category_dataset pcds WITH (NOLOCK)
ON (
pcds.product_category_dataset_id = cvc.product_category_dataset_id
)
INNER JOIN activity_dataset acds WITH(NOLOCK)
ON (
cvc.activity_dataset_id = acds.activity_dataset_id
)
INNER JOIN activity_category_dataset acd WITH(NOLOCK)
ON (
acd.activity_category_dataset_id = acds.activity_category_dataset_id
)
INNER JOIN customer_dataset cust WITH(NOLOCK)
ON (
cust.customer_dataset_id = cvc.customer_dataset_id
)
INNER JOIN rcc r WITH(NOLOCK)
ON (
cvc.rcc_id = r.rcc_id
)
INNER JOIN unit u WITH(NOLOCK)
ON (
u.unit_id = r.unit_id
)
INNER JOIN site s WITH(NOLOCK)
ON (
s.site_id = u.site_id
)
INNER JOIN company_dataset comp WITH(NOLOCK)
ON (
comp.company_dataset_id = s.company_dataset_id
)
INNER JOIN company_dataset DestComp WITH(NOLOCK)
ON (
DestComp.company_dataset_id = pc.company_dataset_id
)
INNER JOIN (
SELECT
rcc_id,
common_cost_rcc_id,
cc_group
FROM
#tblRCCCommanMOGEValues
) rcc_moge
ON (
rcc_moge.rcc_id = cvc.rcc_id
AND ISNULL(rcc_moge.common_cost_rcc_id, -1) = ISNULL(cvc.common_cost_rcc_id, -1)
AND ISNULL(rcc_moge.cc_group, 255) = ISNULL(cvc.cc_group, 255)
)
INNER JOIN rcc_value_attr rva
ON (
rva.rcc_value_attr_id = r.rcc_value_attr_id
)
WHERE
cvc.driver_id <> 8
GROUP BY
r.rcc_id,
comp.company_name,
r.rcc_code,
r.rcc_name,
acd.activity_cat_code,
acds.activity_code,
acds.activity_name,
pc.profit_centre_name,
cd.channel_name,
pcds.product_cat_name,
acd.global_stream_id,
comp.company_code,
pc.profit_centre_code,
cd.channel_code,
pcds.product_cat_code,
cvc.cc_group,
DestComp.company_code,
DestComp.company_name,
cust.customer_level_code,
cust.customer_level_name,
rcc_moge.common_cost_rcc_id,
rva.rcc_value_attr_name,
cvc.customer_dataset_id
Rows availability:
select count(*) from #tblChannelCost WHERE driver_id <> 8
-- 923004
select count(profit_centre_id) from profit_centre pc
-- 2037
select count(channel_dataset_id) from channel_dataset cd
-- 2204
select count(product_category_dataset_id) from product_category_dataset
-- 119767
select count(activity_dataset_id) from activity_dataset
-- 10320
select count(activity_category_dataset_id) from activity_category_dataset
-- 8671
select count(customer_dataset_id) from customer_dataset
-- 12736
select count(rcc_id) from rcc
-- 31729
select count(unit_id) from unit
-- 11093
select count(site_id) from site
-- 913
select count(company_dataset_id) from company_dataset
-- 176
select count(*) from company_dataset
-- 176
Index details:
CREATE NONCLUSTERED INDEX idx_tblChannelCost_profit_centre_id ON #tblChannelCost(profit_centre_id)
CREATE NONCLUSTERED INDEX idx_tblChannelCost_channel_dataset_id ON #tblChannelCost(channel_dataset_id)
CREATE NONCLUSTERED INDEX idx_tblChannelCost_product_category_dataset_id ON #tblChannelCost(product_category_dataset_id)
CREATE NONCLUSTERED INDEX idx_tblChannelCost_activity_dataset_id ON #tblChannelCost(activity_dataset_id)
CREATE NONCLUSTERED INDEX idx_tblChannelCost_customer_dataset_id ON #tblChannelCost(customer_dataset_id)
CREATE NONCLUSTERED INDEX idx_tblChannelCost_rcc_id ON #tblChannelCost(rcc_id)
CREATE NONCLUSTERED INDEX idx_tblChannelCost_common_cost_rcc_id ON #tblChannelCost(common_cost_rcc_id)
CREATE NONCLUSTERED INDEX idx_tblChannelCost_cc_group ON #tblChannelCost(cc_group)
CREATE NONCLUSTERED INDEX idx_tblChannelCost_driver_id ON #tblChannelCost(driver_id)
CREATE CLUSTERED INDEX pk_profit_centre ON profit_centre(profit_centre_id)
CREATE CLUSTERED INDEX pk_channel_dataset ON channel_dataset(channel_dataset_id)
CREATE CLUSTERED INDEX pk_product_category_dataset ON product_category_dataset(product_category_dataset_id)
CREATE CLUSTERED INDEX pk_activity_dataset ON activity_dataset(activity_dataset_id)
CREATE CLUSTERED INDEX pk_activity_category_dataset ON activity_category_dataset(activity_category_dataset_id)
CREATE CLUSTERED INDEX pk_customer_dataset ON customer_dataset(customer_dataset_id)
CREATE CLUSTERED INDEX pk_rcc ON rcc(rcc_id)
CREATE CLUSTERED INDEX pk_unit ON unit(unit_id)
CREATE CLUSTERED INDEX pk_site ON site(site_id)
CREATE CLUSTERED INDEX pk_company_dataset ON company_dataset(company_dataset_id)
November 6, 2013 at 7:44 am
Please post table definitions and execution plan. Also consider getting rid of those nolocks, they're not go-faster options they can result in incorrect results.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 6, 2013 at 10:37 am
You are using almost 15 INNER JOINS that's also not very good idea.
November 7, 2013 at 1:06 am
How can I save the execution plan also can't take screenshot as it runs more than 1 screen!
November 7, 2013 at 1:07 am
Right click plan -> save as. Please don't take a screenshot, pictures of the plan are near-useless.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 7, 2013 at 8:03 am
As I mentioned earlier it's a part in the stored procedure and there are lot more queries. I wish to know how to take the Execution plan for this query alone so that I can insert it here.
November 8, 2013 at 6:38 am
The issue has been resolved! Thanks for everyone! 🙂
November 8, 2013 at 7:20 am
Can you please post what you did to resolve your issue so that others will get benefit from that if they face similar kind of issse in performance?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 8, 2013 at 7:28 am
Indexing only resolved the issue. It increased the performance by 20%. But it's fine now but even though the expected was 70%.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply