Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL 2005 T-SQL Performance tuning Expand / Collapse
Author
Message
Posted Wednesday, November 6, 2013 7:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 30, 2014 5:53 AM
Points: 108, Visits: 50
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)


Post #1511882
Posted Wednesday, November 6, 2013 7:44 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 40,456, Visits: 36,912
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1511886
Posted Wednesday, November 6, 2013 10:37 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, November 23, 2014 9:00 PM
Points: 611, Visits: 448
You are using almost 15 INNER JOINS that's also not very good idea.

Post #1511955
Posted Thursday, November 7, 2013 1:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 30, 2014 5:53 AM
Points: 108, Visits: 50
How can I save the execution plan also can't take screenshot as it runs more than 1 screen!
Post #1512124
Posted Thursday, November 7, 2013 1:07 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 40,456, Visits: 36,912
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1512125
Posted Thursday, November 7, 2013 8:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 30, 2014 5:53 AM
Points: 108, Visits: 50
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.
Post #1512287
Posted Friday, November 8, 2013 6:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 30, 2014 5:53 AM
Points: 108, Visits: 50
The issue has been resolved! Thanks for everyone! :)
Post #1512644
Posted Friday, November 8, 2013 7:20 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:12 AM
Points: 1,953, Visits: 2,397
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/
Post #1512665
Posted Friday, November 8, 2013 7:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 30, 2014 5:53 AM
Points: 108, Visits: 50
Indexing only resolved the issue. It increased the performance by 20%. But it's fine now but even though the expected was 70%.
Post #1512679
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse