June 29, 2006 at 10:33 am
Nice one, Pam!
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 29, 2006 at 10:42 am
*curtsies*
Thanks, Tim.
June 29, 2006 at 3:01 pm
I rewrote your query keeping original logic:
SELECT
UC.UploadControlID,
LEFT(C.CampaignCode, 2) AS CampaignYear,
RIGHT(C.CampaignCode, 3) AS CampaignNumber,
LEFT(HD.AS400Description, 5) AS CampaignSelection,
SA.TitleID AS Sku, SA.StoreID AS StoreNumber,
SA.CurrentAllocation AS Quantity,
CA.SupplierID AS SupplierCode,
CONVERT(Decimal(10, 2), CA.DealerPrice) AS DealerPrice,
CA.DiscountRate AS Discount,
RetailPrice = CASE
WHEN @CampaignType = @TypeSales THEN COALESCE((CONVERT(Numeric(10,2), CA.RetailPrice)),0)
ELSE COALESCE((CONVERT(Numeric(10,2), CA.CampaignWasPrice)),0)
END,
@EDIOption AS OrderItemStatus,
DeliverTo = CASE WHEN S.StoreWarehouse = 0 THEN 'S' ELSE 'W' END,
0 AS OverrideDeliveryLoc,
' ' AS OrderReference,
CA.NetDealerPrice AS NetDealerPrice,
CA.NetDealerPrice AS NetDealerPrice2,
UT.SumTitle + 1 AS SumTitle,
WasPrice = CASE
WHEN @CampaignType = @TypeSales THEN COALESCE((CONVERT(Numeric(10,2), CA.CampaignWasPrice)),0)
ELSE 0.00
END
FROM dbo.Stores S
INNER JOIN dbo.StoreAllocation SA ON S.StoreID = SA.StoreID
INNER JOIN dbo.CampaignAllocation CA ON SA.CampaignAllocationID = CA.CampaignAllocationID
INNER JOIN dbo.UploadControl UC ON CA.CampaignID = UC.CampaignID
INNER JOIN dbo.vuUploadTitleTotal UT ON UC.UploadControlID = UT.UploadControlID AND
CA.SupplierID = UT.SupplierID AND CA.TitleID = UT.TitleID
INNER JOIN dbo.Campaign C ON UC.CampaignID = C.CampaignID
INNER JOIN dbo.HMVCASDepartments HD ON C.HMVCASDepartmentID = HD.HMVCASDepartmentID
INNER JOIN dbo.CampaignAllocation CA1 ON CA1.CampaignID = SA.CampaignID
AND CA1.SupplierID = SA.SupplierID AND CA1.AllocationUploadStatusID <> 1 AND AllocationEDIReadyStatusID = 1
INNER JOIN dbo.CampaignBudget CB ON SA.CampaignID = CB.CampaignID
AND SA.StoreID = CB.StoreID AND CB.CampaignID = SA.CampaignID AND CB.StoreLocked = 0
INNER JOIN dbo.Titles T ON T.TitleID = SA.TitleID
INNER JOIN dbo.UploadSuppliers RS ON RS.RecordingCompanyID = T.RecordingCompanyID
AND RS.SupplierID = SA.SupplierID
INNER JOIN dbo.UploadSuppliers US ON US.SupplierID = CA.SupplierID
WHERE SA.CampaignID = @CampaignID
AND RS.UploadControlID = @UploadControlID
AND US.UploadControlID = @UploadControlID
ORDER BY CA.SupplierID, SA.StoreID
It must be faster now. But somebody must look at your design.
You are joining to dbo.CampaignAllocation twice with different join criteria. Are you sure it's right?
How many tables must have columns CampaignID, SupplierID, UploadControlID?
As soon as you get rid of the mess in your data you'll forget about performance issues in your queries.
_____________
Code for TallyGenerator
June 30, 2006 at 4:21 am
Hi, I tried the version above but it just ran and ran and I had to kill it after a few minutes.
I also tried redesigning it myself with no subqueries but I was having no joy there either.
Anyway, at the moment it takes 15-20 secs to return 100,000+ rows so I'm not concerned about this for now - I've got other performance issues much worse than this I need to deal with first!
Thanks for all your help.
June 30, 2006 at 10:49 pm
I have restriction in my current project - no more than 2 sec. respond for summary query on 2 mil. rows.
Currently I keep it within 0.5 sec.
Just for you to have an idea about perfomance.
_____________
Code for TallyGenerator
July 3, 2006 at 3:09 am
I don't think its so much the number of rows in the tables as the number of tables I am joining. I am sure I can improve the performance but I don't think it will be by much.
Having said that, one of the tables in my query, StoreAllocation, has 18.5 million rows in it.
I ran another simple query over a single table containing 45m rows:
SELECT SalesYear, SalesWeek, StoreID, TitleID, BusinessDate, BackCatalogue, Chart
FROM ProductSales
Where SalesYear = 2005 AND SalesWeek = 5
That took about 16 seconds to return 700K rows..
July 3, 2006 at 6:17 am
And of course yours is in any case not a 'summary' query - which I assume means one with a significant degree of grouping.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply