Query Optimisation

[sqlblind]

Hi Guys,

I was wondering if i could get a helping hand in optimizing this query. I know its not perfectly formed and would really appreciate your expert guidance.

Select ISNull(a.pd_main_group_code_desc,b.pd_main_group_code_desc),
a.pd_super_group_desc ,a.Value_On_Promo,b.Value_On_Multi_Buy,
a.Profit_On_Promo,b.Profit_On_Multi_Buy from (
SELECT
pd_main_group_code,
pd_main_group_code_desc,
pd_super_group_desc,
sum(total_Price) AS Value_On_Promo,
sum(company_margin)AS Profit_On_Promo
FROM
Daily_Summary_Agg ds inner join Date_Dim dd on ds.date_id = dd.date_id
WHERE
po_code <> '0000'
AND pd_super_group_code NOT IN (95,93,99)
AND pd_main_group_code = (select pd_main_group_code from Product_Dim where pd_key = '665777' Group by pd_main_group_code)
AND dd.last_12_mths_flag = 'Y'
GROUP BY
pd_main_group_code_desc,
pd_main_group_code,
pd_super_group_desc)a
full outer Join (
SELECT
pd_main_group_code_desc,
pd_main_group_code,
sum(total_Price) Value_On_Multi_Buy,
sum(company_margin) Profit_On_Multi_Buy,
pd_super_group_desc
FROM
Daily_Summary_Agg ds inner join Date_Dim dd on ds.date_id = dd.date_id
WHERE
mb_deal_number <> '0000'
AND pd_super_group_code NOT IN (99,95,93)
AND pd_main_group_code = (select pd_main_group_code from Product_Dim where pd_key = '665777' Group by pd_main_group_code)
AND dd.last_12_mths_flag = 'Y'
GROUP BY
pd_main_group_code_desc,
pd_main_group_code,
pd_super_group_desc)b
On a.pd_main_group_code = b.pd_main_group_code

 

[gmmastros]

1. First, how long does this query usually take to execute?
2. What would an acceptable execution time be?

I notice you are using 2 subqueries, and they are both the same. I'm talking about this part:

(select pd_main_group_code from Product_Dim where pd_key = '665777' Group by pd_main_group_code)

The query in parenthesis returns a single value, right? I mean... you have 1 column and you're grouping on the column so there can be only 1 row. To determine if this is causing the performance issues (and I suspect it is), can you execute that query by itself, get the value, and then hard code it in to your original query.

I'm not suggesting you leave it this way. Only do this as a troubleshooting step.

After doing this, please let me know how long it takes to execute. If we can determine that this is the problem, then we can start working on a solution.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sqlblind]

Hi George

Thanks for your quick response. i am using two queries, and they are very similar about from these to lines:

po_code <> '0000
mb_deal_number <> '0000'

The sub queries (select pd_main_group_code from Product_Dim where pd_key = '665777' Group by pd_main_group_code) do return a signal value, however i cant hard code this as it needs to be dynamic, the '665777' will become a parameter. It does run faster if I hard code this though!!!

I was just wondering whether there is a better way to write this query? The query itself doesn't take very long to execute, about 6 seconds, however i do need it fast than this.

thanks again

 

[gmmastros]

The easiest way to correct your sub query problem would be to write a multi-step stored procedure, and then to call the stored procedure. You could do something like this:

[!]
SET NOCOUNT ON

Declare @MainGroupCode Int -- Change the data type if necessary

select @MainGroupCode = pd_main_group_code 
from   Product_Dim 
where  pd_key = '665777' 
Group by pd_main_group_code[/!]

Select ISNull(a.pd_main_group_code_desc,b.pd_main_group_code_desc),
a.pd_super_group_desc ,a.Value_On_Promo,b.Value_On_Multi_Buy,
a.Profit_On_Promo,b.Profit_On_Multi_Buy from (
SELECT
pd_main_group_code,
pd_main_group_code_desc,
pd_super_group_desc,
sum(total_Price) AS Value_On_Promo,
sum(company_margin)AS Profit_On_Promo
FROM
Daily_Summary_Agg ds inner join Date_Dim dd on ds.date_id = dd.date_id
WHERE
po_code <> '0000'
AND pd_super_group_code NOT IN (95,93,99)
AND pd_main_group_code = [!]@MainGroupCode[/!]
AND dd.last_12_mths_flag = 'Y'
GROUP BY
pd_main_group_code_desc, 
pd_main_group_code, 
pd_super_group_desc)a
full outer Join (	
SELECT
pd_main_group_code_desc,
pd_main_group_code,
sum(total_Price) Value_On_Multi_Buy,
sum(company_margin) Profit_On_Multi_Buy,
pd_super_group_desc
FROM
Daily_Summary_Agg ds inner join Date_Dim dd on ds.date_id = dd.date_id
WHERE
mb_deal_number <> '0000'
AND pd_super_group_code NOT IN (99,95,93)
AND pd_main_group_code = [!]@MainGroupCode[/!]
AND dd.last_12_mths_flag = 'Y'
GROUP BY
pd_main_group_code_desc, 
pd_main_group_code, 
pd_super_group_desc)b
On a.pd_main_group_code = b.pd_main_group_code

This allows you to change the value while still getting the good performance. There may also be other things that will further improve the performance.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DaveInIowa]

Would this be of any use?

SELECT TOP 1 @MainGroupCode = pd_main_group_code
  FROM Product_Dim
 WHERE pd_key = '665777'

WITH d As (
SELECT date_id
  FROM Date_Dim
 WHERE last_12_mths_flag = 'Y'
)

SELECT pd_main_group_code
       MAX(CASE WHEN po_code <> '0000' THEN pd_main_group_code_desc ELSE NULL) As PromoDesc,
       MAX(CASE WHEN mb_deal_number <> '0000' THEN pd_main_group_code_desc ELSE NULL) As MultiBuyDesc,
       MAX(CASE WHEN po_code <> '0000' THEN pd_super_group_desc ELSE NULL) As PromoSuperDesc,
       MAX(CASE WHEN mb_deal_number <> '0000' THEN pd_super_group_desc ELSE NULL) As MultiBuySuperDesc,
       SUM(CASE WHEN po_code <> '0000' THEN total_Price ELSE 0 END) As Value_On_Promo,
       SUM(CASE WHEN mb_deal_number <> '0000' THEN total_Price ELSE 0 END) As Value_On_Multi_Buy,
       SUM(CASE WHEN po_code <> '0000' THEN company_margin ELSE 0 END) As Profit_On_Promo,
       SUM(CASE WHEN mb_deal_number <> '0000' THEN company_margin ELSE 0 END) As Profit_On_Multi_Buy
  FROM Daily_Summary_Agg 
 WHERE (po_code <> '0000' OR mb_deal_number <> '0000')
   AND pd_super_group_code NOT IN (93,95,99)
   AND pd_main_group_code = @MainGroupCode
   AND date_id IN (SELECT date_id FROM d)
 GROUP BY pd_main_group_code