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-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:32 AM
Points: 39,905, Visits: 36,245
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: Today @ 10:42 AM
Points: 611, Visits: 440
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-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:32 AM
Points: 39,905, Visits: 36,245
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 2:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 19, 2014 12:28 AM
Points: 43, Visits: 509
you are using more than one query altogether.
some queries to build the temp table and
one to join main and temp table.

So please post execution plan of query/ queries which is most expensive.


===========================================
performance issue:
(1) Have you update the statistics on all tables of underling query?
(2) Are you using index well on the tables?
(3) Can you simplify your Query by re-writing it .
(4) are indexes are Defragmented well ?

Post #1512164
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: Tuesday, September 9, 2014 3:27 AM
Points: 1,893, Visits: 2,329
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