Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Tuning a query that takes 60 minutes to run,


Tuning a query that takes 60 minutes to run,

Author
Message
rajsin7786
rajsin7786
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 151
Hello,

I have a SQL query that takes 60 minutes to complete, the query is selecting data from 4 tables, out of which one is a fact table having 1.5 billion records. I am pasting the query below, one thing i noticed was, if i remove the product_dim table from the SQL, then it runs in 8 minutes . Would be great if someone could advise on this, thanks in advance.

SQL taking 50 minutes,

SELECT
Live.dbo.sales_fact.trans_ref,
Live.dbo.product_dim.pd_key,
Live.dbo.product_dim.pd_desc,
Live.dbo.multibuy_dim.mb_deal_number,
Live.dbo.sales_fact.LoyaltyNo,
sum(Live.dbo.sales_fact.qty),
sum(Live.dbo.sales_fact.totalPriceExVat),
( sum(Live.dbo.sales_fact.company_margin) ) - isnull((( sum(Live.dbo.sales_fact.wastageval) )),0)
FROM
Live.dbo.multibuy_dim INNER JOIN Live.dbo.sales_fact ON (Live.dbo.sales_fact.multibuy_id=Live.dbo.multibuy_dim.mb_id)
INNER JOIN Live.dbo.product_dim ON (Live.dbo.sales_fact.product_id=Live.dbo.product_dim.pd_id)
INNER JOIN Live.dbo.date_dim ON (Live.dbo.date_dim.date_id=Live.dbo.sales_fact.date_id)

WHERE
(
Live.dbo.product_dim.pd_key IN ('481005', '850972', '860091', '860101', '860110', '860130', '860159', '860161', '860211', '860224', '860225', '860230', '860265', '860319', '860320', '860344', '860360', '860407', '860414', '860415', '860418', '860469', '860478', '871005', '874115', '874206', '880100', '881153', '890061', '890299', '890360', '890648', '890650', '890651', '891903')
AND
(
Live.dbo.multibuy_dim.mb_deal_number = '0000'
OR
Live.dbo.multibuy_dim.mb_deal_number Is Null
)
AND
( ( Live.dbo.date_dim.date ) between DATEADD(WEEK, -3, (select min(date) from date_dim where last_week_prior_flag = 'Y')) AND (select max(date) from Live.dbo.date_dim where Live.dbo.date_dim.last_week_prior_flag= 'Y' ) )
)
GROUP BY
Live.dbo.sales_fact.trans_ref,
Live.dbo.product_dim.pd_key,
Live.dbo.product_dim.pd_desc,
Live.dbo.multibuy_dim.mb_deal_number,
Live.dbo.sales_fact.LoyaltyNo


SQL taking 8 minutes, ( i removed product_dim from the query)

SELECT
Live.dbo.sales_fact.trans_ref,
Live.dbo.multibuy_dim.mb_deal_number,
Live.dbo.sales_fact.LoyaltyNo,
sum(Live.dbo.sales_fact.qty),
sum(Live.dbo.sales_fact.totalPriceExVat),
( sum(Live.dbo.sales_fact.company_margin) ) - isnull((( sum(Live.dbo.sales_fact.wastageval) )),0)
FROM
Live.dbo.multibuy_dim INNER JOIN Live.dbo.sales_fact ON (Live.dbo.sales_fact.multibuy_id=Live.dbo.multibuy_dim.mb_id)
INNER JOIN Live.dbo.date_dim ON (Live.dbo.date_dim.date_id=Live.dbo.sales_fact.date_id)

WHERE
(
(
Live.dbo.multibuy_dim.mb_deal_number = '0000'
OR
Live.dbo.multibuy_dim.mb_deal_number Is Null
)
AND
( ( Live.dbo.date_dim.date ) between DATEADD(WEEK, -3, (select min(date) from date_dim where last_week_prior_flag = 'Y')) AND (select max(date) from Live.dbo.date_dim where Live.dbo.date_dim.last_week_prior_flag= 'Y' ) )
)
GROUP BY
Live.dbo.sales_fact.trans_ref,
Live.dbo.multibuy_dim.mb_deal_number,
Live.dbo.sales_fact.LoyaltyNo
Eirikur Eiriksson
Eirikur Eiriksson
SSCrazy Eights
SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)

Group: General Forum Members
Points: 8032 Visits: 18162
Quick question, can you post the execution plans?
Cool
rajsin7786
rajsin7786
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 151
Hi Eirikur Eiriksson,

Thanks for your response

I've attached two SQL plans here, one is with product_dim and one without it. do let me know if you are unable to find them.

Thanks again,
Attachments
SQL with product_dim.sqlplan (41 views, 175.00 KB)
SQL without product_dim.sqlplan (12 views, 97.00 KB)
Sean Lange
Sean Lange
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18164 Visits: 17172
rajsin7786 (7/25/2014)
Hi Eirikur Eiriksson,

Thanks for your response

I've attached two SQL plans here, one is with product_dim and one without it. do let me know if you are unable to find them.

Thanks again,


These are the estimated execution plans. We need the actual execution plans if you can get them. Take a look at Gail's article about posting performance problems. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)

Group: General Forum Members
Points: 9616 Visits: 18419
In the meantime, I'd suggest you to change the coding style as 3-part and 4-part names in the column list are deprecated and will be removed on a future version of SQL Server. ;-)


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Lynn Pettis
Lynn Pettis
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26384 Visits: 38120
Also, it would help to know what indexes are defined on the tables.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
rajsin7786
rajsin7786
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 151
Hi Gurus,

Sorry for the late reply, attaching the actual execution plan for your reference. The query was generated from a BI tool that's the reason why its got 3part 4part names in the column, however, ill accept your suggestion and will work with the BI team on this.

All the columns in the where conditions of the SQL statements have indexes defined on them. if u have any more questions please let me know.

Thanks
Attachments
Sean Lange
Sean Lange
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18164 Visits: 17172
rajsin7786 (7/29/2014)
Hi Gurus,

Sorry for the late reply, attaching the actual execution plan for your reference. The query was generated from a BI tool that's the reason why its got 3part 4part names in the column, however, ill accept your suggestion and will work with the BI team on this.

All the columns in the where conditions of the SQL statements have indexes defined on them. if u have any more questions please let me know.

Thanks


The table definitions including indexes would help.

When is the last time you updated your statistics? They seem to be pretty stale.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
rajsin7786
rajsin7786
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 151
Hi, the stats were run 3 weeks back. i have attached the create table scripts for 4 tables. will paste the index soon.
Attachments
tables.rar (7 views, 3.00 KB)
Sean Lange
Sean Lange
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18164 Visits: 17172
rajsin7786 (7/30/2014)
Hi, the stats were run 3 weeks back. i have attached the create table scripts for 4 tables. will paste the index soon.


How much data movement is in these tables?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search