Variable versus explicit value

  • 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

  • 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.

  • 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.

  • 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

  • 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.

  • 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...

  • Is your variable the right datatype?

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

  • @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.

  • @CampaignID is a smallint, which is consistent with how it is defined in the tables as well.

  • Better post the execution plans then.

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

  • >>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.

  • 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.

  • 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.

  • 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

  • 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