CODEWITHSUNDEEP
sql-servert-sqlstored-proceduressql-server-2008-r2cursor
vision
vision

Reputation: 455

Cursors are taking time on my Stored Procedure?

I Have two cursors in stored procedure under SQL Server 2008 r2.They are killing my time when stored proc is executed? i have 2 tables 

##TEMP

CatalogID    Value
34567         80
34848         100
34725         40


##Temp1

   Name    Percentage
     A       25
     B       25
     C       25
     D       25

By using both temp tables iam inserting data in to catalog table and deleting entered catalogid and value.

catalogtable



 CatalogID    name    value

  34567          A      20
  34567          B      20
  34567          C      20
  34567          D      20
  34848          A      25
  34848          B      25
  34848          C      25
  34848          D      25
  34725          A      10
  34725          B      10
  34725          C      10
  34725          D      10

My Cursor is

DECLARE Cur_Rotation CURSOR
FOR 
                  SELECT CatalogId,Value FROM ##TEMP
                   DECLARE  @CatalogId INT
                   DECLARE @Value [decimal](5, 2)
                   OPEN Cur_Rotation
                   FETCH NEXT FROM Cur_Rotation INTO @CatalogId,@Value
                   While @@FETCH_STATUS = 0
BEGIN
           DECLARE Cur_Inner CURSOR
           FOR
                              SELECT Name,Percentage FROM ##Temp1
                              DECLARE @Name VARCHAR(50)
                              DECLARE @Percentage [decimal](5, 2)
                              OPEN Cur_Inner
                              FETCH NEXT FROM Cur_Inner   INTO @Name,@Percentage
                              While @@FETCH_STATUS = 0
BEGIN

                             DECLARE @Value1 [decimal](5, 2) 

                             SET @Value=@Value1*(@Percentage/100.00)
               DELETE FROM  CatalogDetails WHERE CatalogId=@CatalogId  and name=@name
                INSERT INTO CatalogDetails (name,RDDRotPcent,CatalogId)
                                VALUES (@name,@Value1,@CatalogId) 
        FETCH NEXT FROM Cur_Inner  INTO @Name,@Percentage
END
CLOSE Cur_Inner
DEALLOCATE Cur_Inner 
                        FETCH NEXT FROM Cur_Rotation INTO @CatalogId,@Value
END                        

CLOSE Cur_Rotation
DEALLOCATE Cur_Rotation
END

Is there any chance to use any logic to skip cursors .CatalogID will be thousands at that time my query execution is taking time.So is there any chance to change my SCRIPT to avoid cursors.

Upvotes: 1

Views: 912

Answers (3)

SerenityNow
SerenityNow

Reputation: 1065

THis works and adresses your entire question I believe

--Prepare data

DECLARE @Temp1 AS TABLE
(
    CatalogId INT,
    VALUE DECIMAL(19,5)
    )

    INSERT INTO @Temp1 SELECT 34567, 80
    INSERT INTO @Temp1 SELECT 34848, 100
    INSERT INTO @Temp1 SELECT 34725, 40


DECLARE @CatalogDetails AS TABLE
(Id INT PRIMARY KEY IDENTITY(1,1), NAME NVARCHAR(100), RDDRotPcent DECIMAL(19,5), CatalogId INT)

INSERT INTO @CatalogDetails SELECT 'A', .99, 12345
INSERT INTO @CatalogDetails SELECT 'B', .99, 34567


DECLARE @Temp2 AS TABLE
( NAME NVARCHAR(100),
 Percentage DECIMAL(19,5))

 INSERT INTO @Temp2 SELECT 'A', .25
 INSERT INTO @Temp2 SELECT 'B', .25
 INSERT INTO @Temp2 SELECT 'C', .25
 INSERT INTO @Temp2 SELECT 'D', .25


 DECLARE @Catalog AS TABLE
 (CatalogId INT, NAME NVARCHAR(100), VALUE DECIMAL(19,5))
--Fill Catalog with new Data
 INSERT INTO @Catalog

         SELECT CatalogId, NAME, Value * Percentage FROM @Temp1, @Temp2

--Delete Old Values
         DELETE FROM @CatalogDetails WHERE Id IN (SELECT Id FROM @CatalogDetails CD Inner JOIN @Catalog C ON CD.CatalogId = C.CatalogId AND CD.Name = C.NAME)

--Insert New Values

         INSERT INTO @CatalogDetails SELECT Name, Value, CatalogId FROM @Catalog

--View End Result
         SELECT * FROM @CatalogDetails

Upvotes: 2

roman
roman

Reputation: 117370

In your case you can do this without cursors and even without recursive queries:

insert into catalogtable (CatalogID, name, value)
select
    t.CatalogID,
    t1.name,
    t.Value * t1.Percentage / 100.00
from #TEMP as t
    cross join #TEMP1 as t1

sql fiddle demo

Upvotes: 5

Rahul Tripathi
Rahul Tripathi

Reputation: 172418

Not very sure how much it is helpful. But you may try something like this to avoid cursor:-

DECLARE @number_rows     int
DECLARE @count           int
DECLARE @selected     int

DECLARE @table1 TABLE (Id int not null primary key identity(1,1), col1 int )  
INSERT into @table1 (col1) SELECT col1 FROM table2
SET @number_rows=@@ROWCOUNT

SET @count=0
WHILE @count<@number_rows     
BEGIN
    SET @count=@count+1
    SELECT 
        @selected=col1
        FROM @table1
        WHERE Id=@count

    --do your stuff here--

END

Upvotes: 1

Related Questions