CODEWITHSUNDEEP
sql
Hani Muhamed
Hani Muhamed

Reputation: 41

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

Upvotes: 0

Views: 98

Answers (3)

Hani Muhamed
Hani Muhamed

Reputation: 41

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.

Upvotes: 1

ubaldisney
ubaldisney

Reputation: 66

Try this

create PROCEDURE [dbo].[UpdateProducts]
as 
 DECLARE @productIdCursor AS int

 DECLARE
   countProduct 
 CURSOR FOR
  SELECT productId FROM Products

 OPEN   countProduct
  FETCH NEXT FROM countProduct INTO @productIdCursor
 WHILE @@fetch_status = 0
BEGIN

declare @value decimal(18,2)

set @value=(select      
    (select  ISNULL (SUM (isnull (Qty,0)),0) from BILLDETAILS where Pid = @productIdCursor)          -
    (select  ISNULL (SUM (isnull (Qty,0)),0) from InvoiceDetails where Pid = @productIdCursor)       -
    (select ISNULL (SUM (isnull (Qty,0)),0) from  SalesDetails where Pid = @productIdCursor)
    from Products where ProductId= @productIdCursor)

   update Products 
   set QuantityOnHand  =@value
   where ProductId= @productIdCursor

   print @value
    print @productIdCursor
     FETCH NEXT FROM countProduct INTO @productIdCursor

END

CLOSE countProduct
DEALLOCATE countProduct

Just change the type of the varieble "@value" if your need

Upvotes: 0

diiN__________
diiN__________

Reputation: 7666

I don't get why you are using a WHILE loop to achieve this. You should easily be able to update all products by using JOIN:

UPDATE p
SET p.QuantityOnHand =
    (ISNULL(SUM(ISNULL(b.Qty, 0)), 0) -
     ISNULL(SUM(ISNULL(i.Qty, 0)), 0) -
     ISNULL(SUM(ISNULL(s.Qty, 0)), 0))
FROM Products p
INNER JOIN BillDetails b ON p.ProductId = b.Pid
INNER JOIN InvoiceDetails i ON p.ProductId = i.Pid
INNER JOIN SalesDetails s ON p.ProductId = s.Pid

Upvotes: 5

Related Questions