Query Help.

[Designing Solutions WD]

I have this query that I need to speed up. I don't know where to
start. Any advise would be greatly apreciated

SELECT
centerID AS [Center ID], StoreID AS [Store Code], Region, City AS
City, Owner AS [Owner Name], Brand AS Brand,
(SELECT OrderDate FROM [ORDER] WHERE StoreID = S.StoreID AND revid =
0 AND programid = 4) AS [BS Enrollment Date],
(SELECT count(*) FROM production WHERE P_S_RecID = S.StoreID AND
P_PrintID like '%BrandSmart%' and P_PromoMonth = @Month and
P_PromoYear = @Year) AS [BS Quantity],
CASE S.Brand
WHEN 'Allegra Print & Imaging' THEN 75
WHEN 'American Speedy Printing' THEN 75
WHEN 'Insty-Prints' THEN 100
ELSE 0
END
As [BS HO Quantity],
((SELECT count(*) FROM production WHERE P_S_RecID = S.StoreID AND
P_PrintID like '%BrandSmart%' and P_PromoMonth = @Month and
P_PromoYear = @Year) - CASE
WHEN (SELECT count(*) FROM production WHERE P_S_RecID = S.StoreID
AND P_PrintID like '%BrandSmart%' and P_PromoMonth = @Month and
P_PromoYear = @Year) = 0 then 0
WHEN S.Brand = 'Allegra Print & Imaging' THEN 75
WHEN S.Brand = 'American Speedy Printing' THEN 75
WHEN S.Brand = 'Insty-Prints' THEN 100
ELSE 0
END)
as [Billable BS],
(SELECT count(*) FROM production WHERE P_DateResponded is not null
and P_S_RecID = S.StoreID AND P_PrintID like '%LeadSmart%' and
P_PromoMonth = @Month and P_PromoYear = @Year) AS [BS Leads],
(SELECT OrderDate FROM [ORDER] WHERE StoreID = S.StoreID AND revid =
0 AND programid = 5) AS [LS Enrollment Date],
(SELECT count(*) FROM production WHERE P_S_RecID = S.StoreID AND
P_PrintID like '%LeadSmart%' and P_PromoMonth = @Month and P_PromoYear
= @Year) AS [LS Quantity],
CASE
WHEN S.Brand = 'Insty-Prints' THEN 300
ELSE 0
END
As [LS HO Quantity],
((SELECT count(*) FROM production WHERE P_S_RecID = S.StoreID AND
P_PrintID like '%LeadSmart%' and P_PromoMonth = @Month and P_PromoYear
= @Year) - CASE
WHEN (SELECT count(*) FROM production WHERE P_S_RecID = S.StoreID
AND P_PrintID like '%LeadSmart%' and P_PromoMonth = @Month and
P_PromoYear = @Year) = 0 then 0
WHEN S.Brand = 'Insty-Prints' THEN 300
ELSE 0
END)
as [Billable LS],
'0' as [LS Newsletter Quantity],
'0' as [New Business Quantity],
(Select Original_Record_Count From User_Lists WHERE Type = 'Accudata'
and User_ID = S.StoreID and Order_ID is not null) as [Prospect List
Quantity],
1000 as [Allegra HO Quantity],
50 as [Enrollment Fee],
UCC.[Name] as [Name on CC],
UCC.Type as [CC Type],
UCC.Number as [CC Number],
UCC.Expiration_Date as [CC Exp Date],
SBI.SBI_Address1 as [Address 1],
SBI.SBI_ZipCode as [Zip Code],
(Case
When s.UserCC = 1 then 'No'
ELSE 'Yes'
END) as [Invoiced]
FROM dbo.Stores S LEFT JOIN User_Credit_Cards UCC
ON S.StoreID = UCC.User_ID LEFT JOIN StoresBillingInfo SBI
ON S.StoreID = SBI.SBI_S_StoreID
WHERE
(StoreID NOT LIKE '%test%') AND
(StoreID <'admin') ANd
((SELECT OrderDate FROM [ORDER] WHERE StoreID = S.StoreID AND revid =
0 AND programid = 5) is not null OR
(SELECT OrderDate FROM [ORDER] WHERE StoreID = S.StoreID AND revid =
0 AND programid = 4) is not null)

[Ed Murphy]

Designing Solutions WD wrote:

I have this query that I need to speed up. I don't know where to
start. Any advise would be greatly apreciated

Indexes probably make a lot of difference. Which ones have you
defined? Which ones are more/less restrictive on the data? (e.g.
"30% of the records have StoreID = 'Southwest' but only 1% have
CenterID = 'Phoenix')

The pattern searches on production.P_PrintID and Stores.StoreID
can't be helping matters. How many of each do you have? Assuming
there's another table Printers with P_PrintID as its primary key,
you may be able to speed things up by using the following:

create view BS_Printers as
select P_PrintID from Printers where P_PrintID like '%BrandSmart%'

create view LS_Printers as
select P_PrintID from Printers where P_PrintID like '%LeadSmart%'

Here's a first stab at refactoring. Note the comments. Less redundancy
means less opportunity to screw things up by changing one block but
forgetting to change another. Pre-loading the #quantities temp table
may make the speed better / worse / about the same; unless it makes it
significantly worse, I recommend sticking with it.

-- Column types are educated guesses. Insert "post DDL" rant here.
create table #quantities (
StoreID varchar(10),
PromoMonth int,
PromoYear int,
BS_Quantity int,
LS_Quantity
)
go

insert into #quantities (
StoreID,
PromoMonth,
PromoYear,
BS_Quantity,
LS_Quantity
)
select
P_S_RecID,
P_PromoMonth,
P_PromoYear,
sum(case when P_PrintID like '%BrandSmart%' then 1 else 0 end)
as BS_Quantity,
sum(case when P_PrintID like '%LeadSmart%' then 1 else 0 end)
as LS_Quantity,
-- Did you really mean to combine LeadSmart with BS like this?
sum(case when P_PrintID like '%LeadSmart%'
and P_DateResponded is not null then 1 else 0 end) as BS_Leads
from production
group by P_S_RecID, P_PromoMonth, P_PromoYear
go

SELECT
centerID AS [Center ID],
StoreID AS [Store Code],
Region,
City AS City,
Owner AS [Owner Name],
Brand AS Brand,
o_bs.OrderDate as [BS Enrollment Date],
q.BS_Quantity as [BS Quantity],
CASE S.Brand
WHEN 'Allegra Print & Imaging' THEN 75
WHEN 'American Speedy Printing' THEN 75
WHEN 'Insty-Prints' THEN 100
ELSE 0
END As [BS HO Quantity],
(q.BS_Quantity - case
WHEN q.BS_Quantity = 0 then 0
WHEN S.Brand = 'Allegra Print & Imaging' THEN 75
WHEN S.Brand = 'American Speedy Printing' THEN 75
WHEN S.Brand = 'Insty-Prints' THEN 100
ELSE 0
END) as [Billable BS],
q.BS_Leads AS [BS Leads],
o_ls.OrderDate as [LS Enrollment Date],
q.LS_Quantity AS [LS Quantity],
CASE
WHEN S.Brand = 'Insty-Prints' THEN 300
ELSE 0
END As [LS HO Quantity],
(q.LS_Quantity - CASE
WHEN q.LS_Quantity = 0 then 0
WHEN S.Brand = 'Insty-Prints' THEN 300
ELSE 0
END) as [Billable LS],
'0' as [LS Newsletter Quantity],
'0' as [New Business Quantity],
ul.Original_Record_Count as [Prospect List Quantity],
1000 as [Allegra HO Quantity],
50 as [Enrollment Fee],
UCC.[Name] as [Name on CC],
UCC.Type as [CC Type],
UCC.Number as [CC Number],
UCC.Expiration_Date as [CC Exp Date],
SBI.SBI_Address1 as [Address 1],
SBI.SBI_ZipCode as [Zip Code],
(Case
When s.UserCC = 1 then 'No'
ELSE 'Yes'
END) as [Invoiced]
FROM dbo.Stores S
left join #quantities q
on S.StoreID = q.StoreID and @Month = q.PromoMonth
and @Year = q.PromoYear
LEFT JOIN User_Credit_Cards UCC ON S.StoreID = UCC.User_ID
LEFT JOIN StoresBillingInfo SBI ON S.StoreID = SBI.SBI_S_StoreID
join ORDER o_bs
on S.StoreID = o_bs.StoreID and o_bs.revid = 0
and o_bs.programid = 4
join ORDER o_ls
on S.StoreID = o_ls.StoreID and o_ls.revid = 0
and o_ls.programid = 5
left join User_Lists ul
on S.StoreID = ul.User_ID and ul.Type = 'Accudata'
and ul.Order_ID is not null
WHERE StoreID not like '%test%'
and StoreID <'admin'
and o_bs.OrderDate is not null
and o_ls.OrderDate is not null
go

drop table #quantities
go

[Designing Solutions WD]

Ed,

You are a my hero.

Thank you so much I will give your code a detailed look but that
worked wonders 33 seconds down to 3 second.

Michael