Variable versus explicit value

  • Nice one, Pam!

    Tim Wilkinson

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

  • *curtsies*

    Thanks, Tim.

  • 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

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

  • 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

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

  • 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