June 29, 2006 at 9:14 am
I've got a problem here that's got us utterly flummoxed.
I have a piece of SQL in an SP which returns a set of data. However I am finding it runs very slow (7 minutes), unless I replace one of the variable values with an explicit value, and then it is relatively quick (15 seconds).
The execution plan is different for both versions, but nothing obvious sticks out. Here is the SQL:
SELECT
dbo.UploadControl.UploadControlID,
LEFT(dbo.Campaign.CampaignCode, 2) AS CampaignYear,
RIGHT(dbo.Campaign.CampaignCode, 3) AS CampaignNumber,
LEFT(dbo.HMVCASDepartments.AS400Description, 5) AS CampaignSelection,
SA.TitleID AS Sku, SA.StoreID AS StoreNumber,
SA.CurrentAllocation AS Quantity,
dbo.CampaignAllocation.SupplierID AS SupplierCode,
CONVERT(Decimal(10, 2), dbo.CampaignAllocation.DealerPrice) AS DealerPrice,
dbo.CampaignAllocation.DiscountRate AS Discount,
RetailPrice = CASE
WHEN @CampaignType = @TypeSales THEN COALESCE((CONVERT(Numeric(10,2),CampaignAllocation.RetailPrice)),0)
ELSE COALESCE((CONVERT(Numeric(10,2),CampaignAllocation.CampaignWasPrice)),0)
END,
@EDIOption AS OrderItemStatus,
DeliverTo = CASE
WHEN Stores.StoreWarehouse = 0 THEN 'S'
ELSE 'W'
END,
0 AS OverrideDeliveryLoc,
' ' AS OrderReference,
dbo.CampaignAllocation.NetDealerPrice AS NetDealerPrice,
dbo.CampaignAllocation.NetDealerPrice AS NetDealerPrice2,
dbo.vuUploadTitleTotal.SumTitle + 1 AS SumTitle,
WasPrice = CASE
WHEN @CampaignType = @TypeSales THEN COALESCE((CONVERT(Numeric(10,2),CampaignAllocation.CampaignWasPrice)),0)
ELSE 0.00
END
FROM
dbo.Stores INNER JOIN
dbo.StoreAllocation SA INNER JOIN
dbo.CampaignAllocation ON SA.CampaignAllocationID = dbo.CampaignAllocation.CampaignAllocationID INNER JOIN
dbo.UploadControl INNER JOIN
dbo.vuUploadTitleTotal ON dbo.UploadControl.UploadControlID = dbo.vuUploadTitleTotal.UploadControlID ON
dbo.CampaignAllocation.CampaignID = dbo.UploadControl.CampaignID AND
dbo.CampaignAllocation.SupplierID = dbo.vuUploadTitleTotal.SupplierID AND
dbo.CampaignAllocation.TitleID = dbo.vuUploadTitleTotal.TitleID INNER JOIN
dbo.Campaign ON dbo.UploadControl.CampaignID = dbo.Campaign.CampaignID ON dbo.Stores.StoreID = SA.StoreID INNER JOIN
dbo.HMVCASDepartments ON dbo.Campaign.HMVCASDepartmentID = dbo.HMVCASDepartments.HMVCASDepartmentID
WHERE
(SA.CampaignID = @CampaignID) AND
(SA.TitleID IN
(SELECT TitleID
FROM CampaignAllocation
WHERE CampaignID = @CampaignID AND SupplierID = SA.SupplierID AND AllocationUploadStatusID <> 1 AND AllocationEDIReadyStatusID = 1)) AND
(SA.StoreID IN
(SELECT dbo.StoreAllocation.StoreID
FROM dbo.StoreAllocation INNER JOIN
dbo.CampaignBudget ON dbo.StoreAllocation.CampaignID = dbo.CampaignBudget.CampaignID AND
dbo.StoreAllocation.StoreID = dbo.CampaignBudget.StoreID
WHERE (dbo.CampaignBudget.CampaignID = @CampaignID) AND (dbo.CampaignBudget.StoreLocked = 0)
GROUP BY dbo.StoreAllocation.StoreID)) AND (dbo.UploadControl.UploadControlID = @UploadControlID) AND (SA.TitleID IN
(SELECT TitleID
FROM Titles
WHERE RecordingCompanyID IN
(SELECT RecordingCompanyID
FROM UploadSuppliers
WHERE UploadControlID = @UploadControlID AND SupplierID = SA.SupplierID))) AND (dbo.CampaignAllocation.SupplierID IN
(SELECT SupplierID
FROM UploadSuppliers
WHERE UploadControlID = @UploadControlID))
ORDER BY dbo.CampaignAllocation.SupplierID, SA.StoreID
If I replace @CampaignID with my value 506 then it is quick.
Can anyone give me any ideas as to why this might be? Let me know what other information you need.
Thanks
Kenny
June 29, 2006 at 9:28 am
I have had the same problem in the past. I was never really able to find the exact cause other than the possibility of the fact that with the variable hard codded SQL Server was able to avoid recompiling the procedure.
June 29, 2006 at 9:31 am
I could create dynamic SQL to get round this which I think would fix it but that's a messy way of doing things and future maintenance would be like cracking the Da Vinci code.
June 29, 2006 at 9:36 am
It may be that SQL server has a suboptimal plan based on an atypical value for this param. Try DBCC FREEPROCCACHE and then run the procedure twice with a typical value. If this was the problem, then you should see an improvement the second time you run the proc.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 29, 2006 at 9:43 am
Is the variable @CampaignID a local variable or a passed-in parameter?
I'm guessing it's local. If that's the case, that's your problem as the optimizer can't effectively use local variables to create execution plans.
June 29, 2006 at 9:46 am
Interesting idea. I have run DBCC FREEPROCCACHE and I am running the SP again... although it takes about 7 minutes to run so I'll report back after this time. Hopefully the second time I run it, it will take 15 seconds.
Hello - it's finished earlier - 3 minutes.
Running it a second time now and it's at about a minute now...
June 29, 2006 at 9:50 am
Is your variable the right datatype?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 29, 2006 at 9:51 am
@UploadControlID is a passed in parameter, and @CampaignID is retreived within the SP based on @UploadControlID, so yes it's local.
But if I retrieved it before I call the SP and pass it in, then that shoud resolve that issue.
I think I do that sort of thing quite a lot (ie local variables) so I wonder how many other performance hits I have? Also, this seems to be a serious flaw in the optimizer.
June 29, 2006 at 9:55 am
@CampaignID is a smallint, which is consistent with how it is defined in the tables as well.
June 29, 2006 at 9:58 am
Better post the execution plans then.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 29, 2006 at 10:02 am
>>and @CampaignID is retreived within the SP based on @UploadControlID, so yes it's local.
Why not take whatever query generates @CampaignID and make it a derived table that is joined to in the subsequent SELECT ?
Also, while not the cause of this issue, you should revisit all those IN (SELECT ...) sub-queries. They definitely aren't helping the performance.
June 29, 2006 at 10:04 am
Well well well well!!!!
Passing @CampaignID as a parameter does the trick - it consistently takes 15 seconds now.
I'm really surprised by this. I'm sure most developers are unaware of this and I'm sure I will make LOTS of performance improvements by passing variables as parameters. I would be interested in people's views on this. Is this common knowledge?
Thanks very much for the solution.
June 29, 2006 at 10:08 am
derived table? No idea what that means and it sounds more complicated than simply passing as a parameter!
Sub-queries - yes I know they don't help - I inherited this database, but I'm not entirely sure how to avoid this.
June 29, 2006 at 10:10 am
I don't think this happens in SQL9, as individual statements have their own plans. I stand to be corrected of course.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 29, 2006 at 10:17 am
This is how it was explained to me:
"the optimizer “looks” at the filter values in the WHERE clause and compares that against statistic information stored in the tables and indexes. If the filter value is stored in a variable, the optimizer has no idea what the value is...When a procedure is “saved” to the database, it has no query plan. The query plan will not be generated until the first execution of the procedure. During the first execution, the optimizer will “plug in” the values contained within any parameters as if they where hard coded into the query. Only then will the query be optimized! This allows queries to be tuned as if no variables exist within the query statement but only if the variable was an incoming parameter. "
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply