SQLTeam.com | Weblogs | Forums

Performance improvement


#1

Please let me know how to improve the cursor in below code by replacing with set operations . this cursor logic is calling 2 stored procedures which intern call one more stored procedure.

Declare CursorName1 Cursor for
select Col1,Col2,Col3,Col4 from Tabel1

Open CursorName1
Fetch Next from CursorName1 into @Col1,@Col2,@Col3,@Col4

while @@FETCH_STATUS = 0
BEGIN

EXEC Storedprocedure1
@Col1,@Col2,@Col3

EXEC Storedprocedure2
@Col1,@Col2,@Col4

Fetch Next from CursorName1 into @Col1,@Col2,@Col3,@Col4
End
Close CursorName1
DeAllocate CursorName1

Storedprocedure1

Insert into table2(col1,col2,col3) values (@col1,@col2,@col3)

Storedprocedure2

IF @col1 is not null
begin
EXEC Storedprocedure3 @col1
END
IF @col2 is not null
begin
EXEC Storedprocedure3 @col2
END
IF @col3 is not null
begin
EXEC Storedprocedure3 @col3
END

Storedprocedure3

Insert into table4(col1) values (@col1)


#2
Insert into table2 ( col1, col2, col3 )
Select col1,col2,col3
From Table1

Insert into table4 ( col1 )
Select ca1.col1
From Table1
Cross Apply (
    Values(col1),(col2),(col3)
) As ca1(col1)
Where ca1.col1 IS NOT NULL