Post reply

Problems with good SPs going bad in Agent!

  • May 30, 2007 at 8:50 pm

    #709895

    Yes, it really was "Quit with Success" <G>

    Seems as if I may have eliminated the problem now.

  • May 31, 2007 at 9:00 am

    #710037

    Good to hear you've got a successful completion

    Grey matter has churned over sufficiently ...

    Statistics are used by the query optimiser when it's deciding the best method to retrieve the data for a query. If the statistics for an index are out-of-date then the optimiser won't consider that index when building the execution plan for a query. So even though you have an index it may not be getting used. This article gives a good overview of statistics and how they are used http://www.sql-server-performance.com/statistics.asp

    What you should do is take each query in the procedure and execute it in Query Analyser showing the Execution Plan (Ctrl + K). When you look at the Execution plan you want to eliminate any scans. This includes table, clustered index and non-clustered index scans.

    Another good guide for performance is to run SET STATISTICS IO ON before executining the query. This will show the number of reads performed on each table. Basically the fewer the reads the better. Of course if you're returning millions of rows, you'll have a high number of reads, but if you're only returning a couple of rows, you shouldn't have thousands of reads.

    Looking at the first query in the procedure sfbUpdateProductSalesSummary, heres what I'd consider before even running the query, or viewing an execution plan.

    -- current indexes

    Products: ProductID

    Stores: StoreSysID

    Franchises: MFnum

    DayTrans: StoreSysID, SaleDateTime, ProductID, PurchaseUnitID

    ProductPriceLink: StoreSysID, ProductID, PurchaseUnitID

    -- query

    INSERT INTO #AveragePrices (ProductID, SizeID, AveragePriceExTax)

    SELECT L.ProductID, L.PurchaseUnitID, AVG(L.UnitPrice * (100 / (100 + L.TaxRate)))

    FROM ProductPriceLink L

    JOIN Stores S ON L.StoreSysID = S.StoreSysID

    JOIN Franchises F ON S.MFnum = F.MFnum

    WHERE F.CCode = @CountryCode

    GROUP BY L.ProductID, L.PurchaseUnitID

    -- suggested indexes

    Franchises: CCode -- search criteria

    Stores: MFnum -- join

    ProductPriceLink: ProductID, PurchaseUnitID, UnitPrice, TaxRate -- grouping/data retrieval

    ProductPriceLink: StoreSysID -- join

    Stores: StoreSysID -- join

    Franchises: MFnum -- join

    In your previous post, you don't specify if your indexes a clustered or primary keys, but I'd leave the existing indexes as they are anyway. If you've got an appropriate test environment, add the above indexes as non-clustered indexes one at a time comparing the performance after each index is created. Not all the indexes above may be useful, but this is just from looking at the query and not running it on your data.

    Let me know how you get on.

    --------------------
    Colt 45 - the original point and click interface

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply