How to reduce the execution time for stored procedure using cursors in sql

Question

0.00/5 (No votes)

See more:

Hi
I have written one stored procedure for finding fifo cost using cursors in sql. Every time i pass one product name as a parameter i have more than 1000 products so it checks one by one.That's why it takes more time.

My SP

SQL

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
    ALTER procedure [dbo].[SP_GETFIFOCOST](@pid nvarchar(250)) as
    Begin
    Declare @qty decimal(18,2),@cost decimal(18,2),@line int
    Declare @qty1 decimal(18,2),@cost1 decimal(18,2),@line1 int
    Declare @fqty decimal(18,2)
    set @fqty=0
    Declare MyCur Cursor for select QTY_IN,UNIT_COST,LINE from STOCKDETAILS  where STOCK_JR in ('IN','GR','TR','SA','OS','SR','AS','OG','PO') and PRODUCT_NO=@pid order by STOCK_DATE
    Open MyCur
    Declare MyCur1 Cursor for select QTY_OUT,UNIT_COST,LINE from STOCKDETAILS  where STOCK_JR in ('OU','IS','SJ','TR','SA','CS','AS','PR','DO') and PRODUCT_NO=@pid order by STOCK_DATE
    Open MyCur1
    if @fqty=0
    begin
    Fetch Next From MyCur1 into @qty1,@cost1,@line1
    Fetch Next From MyCur into @qty,@cost,@line
    End
    While @@Fetch_Status=0
    Begin
    if @qty>@qty1
    begin
    set @qty=@qty-@qty1
    update STOCKDETAILS set FIFO_COST=@cost where LINE=@line1
    set @fqty=@fqty+@qty
    if @fqty>0
    begin
    Fetch Next From MyCur1 into @qty1,@cost1,@line1
    set @fqty=@qty-@qty1
    update STOCKDETAILS set FIFO_COST=@cost where LINE=@line1
    end
    end
    if @qty<@qty1
    begin
    set @fqty=@qty-@qty1
    update STOCKDETAILS set FIFO_COST=@cost where LINE=@line1
    if @fqty<0
    begin
    Fetch Next From MyCur into @qty,@cost,@line
    Fetch Next From MyCur1 into @qty1,@cost1,@line1
    set @qty1=@fqty-@qty1
    set @fqty=@qty+@qty1
    if @fqty>0
    begin
    set @qty=@qty+@qty1
    end
    update STOCKDETAILS set FIFO_COST=@cost where LINE=@line1
    end
    end
    if @fqty=0
    begin
    update STOCKDETAILS set FIFO_COST=@cost where LINE=@line1
    Fetch Next From MyCur1 into @qty1,@cost1,@line1
    Fetch Next From MyCur into @qty,@cost,@line
    End
    End
    Close MyCur1
    Close MyCur
    Deallocate MyCur1
    Deallocate MyCur
    End

I have taken one button. If you click that button it will update fifo cost for all product.

I have written code like this in button

C#

try
               {
                   str = "select distinct PRODUCT_NO from PRODUCT order by PRODUCT_NO";
                   cn.Open();
                   cmd.CommandText = str;
                   cmd.Connection = cn;
                   dr = cmd.ExecuteReader();
                   try
                   {
                       while (dr.Read())
                       {
                           using (DbCommand cmdb = cn.CreateCommand())
                           {
                               try
                               {
                                   cmdb.Connection = cn;
                                   cmdb.CommandText = "SP_GETFIFOCOST";
                                   cmdb.CommandType = CommandType.StoredProcedure;
                                   var parameter = cmdb.CreateParameter();
                                   parameter.ParameterName = "@pid";
                                   parameter.Value = dr.GetValue(0).ToString();
                                   cmdb.Parameters.Add(parameter);
                                   cmdb.ExecuteNonQuery();
                               }
                               catch (Exception Ex) { }
                           }
                           //progressBar1.Value++;
                       }
                       dr.Close();
                       cn.Close();
                   }
                   catch (Exception Ex)
                   {
                       dr.Close();
                       cn.Close();
                   }

Would u pls solve this issue

Posted 18-Sep-15 21:00pm

TarunKumarSusarapu

Updated 18-Sep-15 23:33pm

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2015-09-18T21:25:00

Solution 1

The biggest performance problem is the usage of cursors. You should modify the procedure so that you update the tables with a single statement without looping through the cursor results.

I'm not sure about the logic since based on the code it's somewhat unclear what the conditions should be but in overall the idea should be that you run an update statement which uses the values from the parameters of the procedure, not from the cursor.

ADDITION:

From what I've gathered the procedure should return the cost for a product based on stock incomes which are used as FIFO.

This could be far off but using the example data would the query in the end return correct results in case different #stockout rows are added

SQL

CREATE TABLE #StockIn (
   OrderNo int,
   Quantity int,
   Cost int
);

-- Add stock inserts
INSERT INTO #StockIn VALUES (1, 10, 11);
INSERT INTO #StockIn VALUES (2, 10, 12);
INSERT INTO #StockIn VALUES (3, 10, 13);

CREATE TABLE #StockOut (
   OrderNo int,
   Quantity int,
   Cost int
);

-- Add stock usages
INSERT INTO #StockOut VALUES (1, 1, 5);
INSERT INTO #StockOut VALUES (2, 2, 5);
INSERT INTO #StockOut VALUES (3, 12, 5);
INSERT INTO #StockOut VALUES (4, 3, 5);

-- select the current price for an item in stock based on the amount added and used
SELECT TOP 1 *
FROM #StockIn si
WHERE (COALESCE((SELECT SUM(si2.Quantity)
       FROM   #StockIn si2
	   WHERE  si2.OrderNo < si.OrderNo), 0)
	  + si.Quantity) >= (SELECT SUM(so.Quantity)
	                    FROM #StockOut so)
ORDER BY OrderNo;

Posted 18-Sep-15 21:25pm

Wendelius

Updated 19-Sep-15 2:25am

v2

Comments

TarunKumarSusarapu 19-Sep-15 4:41am

Here It checks every record with another record in another table to find the fifo cost that's why i have written two cursors to calculate the fifo cost. Would u please elaborate the code without use cursors so that we can increase the performance.

Wendelius 19-Sep-15 4:51am

Can you explain the idea what to should be updated for each row? Perhaps a data example?

TarunKumarSusarapu 19-Sep-15 5:12am

Previously I have asked one question In that all the logic will be there at front end so that you can easily understand the calculation part.
http://www.codeproject.com/Questions/1029415/How-to-write-a-cursor-in-sql-to-compare-two-table?arn=0

Wendelius 19-Sep-15 7:19am

As far as I can see the post is effectively the same. Please add an example with some data, what you want to achieve. This is necessary in order to transform the logic.

TarunKumarSusarapu 19-Sep-15 5:51am

Have u found out any other ways to calculate this cost?

TarunKumarSusarapu 19-Sep-15 7:40am

Suppose i inserted three invoices for STOCK IN with quantity & cost
10 11
10 12
10 13
i inserted four invoices for STOCK OUT with quantity & cost
1 5
2 5
12 5
3 5
then u have to findout the fifo cost for stock out
for 1 5 invoice stock in -stock out 10-2=9 for this invoice fifo cost 11
for 2 5 invoice 9-2=7 for this invoice also fifo cost 11
similarly
for 12 5 invoice 7-12=-5 that will be added to next invoice qty
i.e 10 12 invoice in STOCK IN so the final qty is 10-5 =5 for next invoice in STOCK OUT 2 5 the fifo cost is 12.
Like that we need to find FIFO QTY by comparing stock in & StockOut

Wendelius 19-Sep-15 8:03am

And the idea would be to know the cost for each stock out row, right? So what should be updated or should the procedure just return the current value for the product based on the stock usage?

Wendelius 19-Sep-15 8:25am

Have a look at the modified answer.

TarunKumarSusarapu 21-Sep-15 0:05am

OK Thank u I will check it once and i will tell u

TarunKumarSusarapu 21-Sep-15 1:09am

Thank u bro its working Keep it up have a bright future bro

Wendelius 21-Sep-15 1:13am

You're welcome :)

TarunKumarSusarapu 30-Sep-15 2:35am

Bro I have a problem in converting mdb to sql database would u pls solve this error
http://www.codeproject.com/Questions/1016561/IErrorInfo-GetDescription-failed-with-E-FAIL-x?arn=0

TarunKumarSusarapu 6-Oct-15 7:39am

I got eeror like this 'ODBC Call Failed' converting mdb to sql database would u pls solve this error
http://www.codeproject.com/Questions/1036469/I-got-error-like-lis-ODBC-Call-Failed-when-importi?arn=0

How to reduce the execution time for stored procedure using cursors in sql

1 solution

Solution 1

Add your solution here

Preview 0

How to reduce the execution time for stored procedure using cursors in sql

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us