Executing stored procedure takes too long

I am trying to update quantity on hand for all the inventory items that I have whenever I make a purchase or a sale. I have a stored procedure that does the job. It runs along fine, but when I have for example 2000 inventory items, execution of the query takes more than 45 minutes – any ideas?

CREATE PROCEDURE [dbo].[UpdateProducts]
AS
BEGIN
    DECLARE @LoopCounter INT , @MaxCode INT, 

    SELECT @LoopCounter = MIN(ProductId), @MaxCode = MAX(ProductId) 
    FROM products

    WHILE (@LoopCounter IS NOT NULL
           AND @LoopCounter <= @MaxCode)
    BEGIN
        UPDATE Products 
        SET QuantityOnHand  = (SELECT 
                                   ((SELECT ISNULL(SUM(ISNULL(Qty, 0)), 0) 
                                     FROM BILLDETAILS 
                                     WHERE Pid = @LoopCounter) -
                                    (SELECT ISNULL(SUM(ISNULL(Qty, 0)), 0) 
                                     FROM InvoiceDetails 
                                     WHERE Pid = @LoopCounter) -
                                    (SELECT ISNULL(SUM(ISNULL(Qty, 0)), 0) 
                                     FROM SalesDetails 
                                     WHERE Pid = @LoopCounter))
        WHERE ProductId= @LoopCounter

        SELECT @LoopCounter = MIN(ProductId) 
        FROM Products
        WHERE ProductId > @LoopCounter
    END  
END

Answer

I fiqured out why my query was slow i had a column that stores the last update date & time of each row.and i was updating that column by using a trigger and that affected performance dramatically.

Advertisement

Answer