Posted by Designing Solutions WD on 04/06/07 16:23

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)

[Back to original message]