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 1234»»»

Tuning a query that takes 60 minutes to run, Expand / Collapse
Author
Message
Posted Friday, July 25, 2014 8:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 5:33 AM
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


Post #1596245
Posted Friday, July 25, 2014 8:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 11:17 AM
Points: 2,000, Visits: 5,448
Quick question, can you post the execution plans?
Post #1596246
Posted Friday, July 25, 2014 8:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 5:33 AM
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,


  Post Attachments 
SQL with product_dim.sqlplan (39 views, 175.04 KB)
SQL without product_dim.sqlplan (9 views, 97.38 KB)
Post #1596261
Posted Friday, July 25, 2014 9:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:47 AM
Points: 12,994, Visits: 12,410
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)
Post #1596280
Posted Friday, July 25, 2014 9:59 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:43 AM
Points: 3,615, Visits: 8,112
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1596303
Posted Friday, July 25, 2014 10:10 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:08 PM
Points: 20,696, Visits: 32,330
Also, it would help to know what indexes are defined on the tables.



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)
Post #1596311
Posted Tuesday, July 29, 2014 2:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 5:33 AM
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


  Post Attachments 
Actualplan.withalltables.sqlplan (20 views, 221.52 KB)
actualplan.withoutproducttable.sqlplan (3 views, 126.65 KB)
Post #1597194
Posted Tuesday, July 29, 2014 7:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:47 AM
Points: 12,994, Visits: 12,410
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)
Post #1597284
Posted Wednesday, July 30, 2014 9:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 5:33 AM
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.

  Post Attachments 
tables.rar (5 views, 3.06 KB)
Post #1597821
Posted Wednesday, July 30, 2014 10:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:47 AM
Points: 12,994, Visits: 12,410
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)
Post #1597834
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse