Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Query Help- Cursor change into FOR WHILE LOOP, TEMP TABLE Expand / Collapse
Author
Message
Posted Wednesday, July 25, 2012 3:23 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 10, 2012 7:32 AM
Points: 57, Visits: 22
I have created a STORED PROCEDURE, which includes 5 different CURSORs. The company I am working for has just informed me that the use of CURSORs is not accepted here. I need help converting my CURSORs into FOR WHILE LOOP format. Someone please help me out. Here is the CURSOR:

CREATE PROCEDURE usp_PreStageValidation
@SQL_str1 NVARCHAR(max),
@SQL_str2 NVARCHAR(max),
@SQL_str3 NVARCHAR(max),
@SQL_str4 NVARCHAR(max),
@SQL_str5 NVARCHAR(max)
AS
BEGIN TRY
DECLARE
@Catalog nvarchar(128),
@Table nvarchar(128),
@Column nvarchar(128),
@Message nvarchar(128);

SET NOCOUNT ON




Declare @cat


Select @cat

EXEC sp_executeSQL @SQL_str1, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName

OPEN syd_cursor1;
FETCH NEXT FROM syd_cursor1 INTO @cat,@tbl;

WHILE @@FETCH_STATUS = 0
BEGIN
@msg = 'This Stage table is not existed in this SOURCE database';
INSERT INTO syd_LogTable VALUES(@cat,@tbl,NULL,@msg);

FETCH NEXT FROM db_cursor1 INTO @cat,@tbl;
END;

CLOSE syd_cursor1;
DEALLOCATE syd_cursor1;



IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = @cat AND --When @cat contains Source Database name
TABLE_SCHEMA = 'dbo' AND
TABLE_NAME = 'tbPlanStartEnd' ) --and this table is not existed in it
BEGIN



INSERT INTO syd_LogTable
VALUES(@cat, 'tbPlanStartEnd', NULL, 'This Table is Created Newly');
END;

--------------

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = @cat AND --When @cat contains Source Database name
TABLE_SCHEMA = 'dbo' AND
TABLE_NAME = 'tbPlanGroup' ) --and this table is not existed in it
BEGIN


INSERT INTO syd_LogTable
VALUES(@cat, 'tbPlanGroup', NULL, 'This Table is Created Newly');
End

ELSE if
NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = @cat AND --When @cat contains Source Database name
TABLE_SCHEMA = 'dbo' AND
TABLE_NAME = 'tbPlanGroup' AND
COLUMN_NAME = 'grp_name' ) --and this column is not existed in it
BEGIN



INSERT INTO syd_LogTable
VALUES(@cat, 'tbPlanGroup', NULL, 'This Table is added with column grp_name');
END;

END;


DECLARE syd_cursor2 CURSOR FOR
EXEC sp_executeSQL @@SQL_str2, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName

OPEN syd_cursor2;
FETCH NEXT FROM syd_cursor2 INTO @cat,@tbl;

WHILE @@FETCH_STATUS = 0
BEGIN
@msg = 'This Stage table is not existed in this STAGE database';
INSERT INTO syd_LogTable VALUES(@cat,@tbl,NULL,@msg);

FETCH NEXT FROM db_cursor2 INTO @cat,@tbl;
END

CLOSE syd_cursor2;
DEALLOCATE syd_cursor2;




DECLARE syd_cursor3 CURSOR FOR
EXEC sp_executeSQL @@SQL_str3, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName

OPEN syd_cursor3;
FETCH NEXT FROM syd_cursor3 INTO @cat,@tbl,@col;

WHILE @@FETCH_STATUS = 0
BEGIN
@msg = 'This Stage Column is not existed in this Table of this SOURCE database';
INSERT INTO syd_LogTable VALUES(@cat,@tbl,@col,@msg);

FETCH NEXT FROM db_cursor3 INTO @cat,@tbl,@col;
END

CLOSE syd_cursor3;
DEALLOCATE syd_cursor3;

DECLARE syd_cursor4 CURSOR FOR
EXEC sp_executeSQL @@SQL_str4, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName

OPEN syd_cursor4;
FETCH NEXT FROM syd_cursor4 INTO @cat,@tbl,@col;

WHILE @@FETCH_STATUS = 0
BEGIN
@msg = 'This Stage Column is not existed in this Table of this STAGE database';
INSERT INTO syd_LogTable VALUES(@cat,@tbl,@col,@msg);

FETCH NEXT FROM db_cursor4 INTO @cat,@tbl,@col;
END;

CLOSE syd_cursor4;
DEALLOCATE syd_cursor4;

DECLARE syd_cursor5 CURSOR FOR
EXEC sp_executeSQL @@SQL_str5, N'@ApplicationName VARCHAR(100)', @ApplicationName=@ApplicationName

OPEN syd_cursor5;
FETCH NEXT FROM syd_cursor5 INTO @cat,@tbl,@col;

WHILE @@FETCH_STATUS = 0
BEGIN
@msg = 'The Data Type of this Stage Column of this Table is NOT same as in this SOURCE database';
INSERT INTO syd_LogTable VALUES(@cat,@tbl,@col,@msg);

FETCH NEXT FROM db_cursor5 INTO @cat,@tbl,@col;
END

CLOSE syd_cursor5;
DEALLOCATE syd_cursor5;
Post #1335448
Posted Wednesday, July 25, 2012 4:07 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:34 PM
Points: 35,609, Visits: 32,200
morepainot (7/25/2012)
I have created a STORED PROCEDURE, which includes 5 different CURSORs. The company I am working for has just informed me that the use of CURSORs is not accepted here. I need help converting my CURSORs into FOR WHILE LOOP format. Someone please help me out. Here is the CURSOR:


I don't have the time today to help you rewrite these but I do have time to tell you that if you're going to convert the cursors to the ol "Temp Table and While Loop", you're absolutely wasting your time. Behind the scenes, a a nice STATIC, FORWARD ONLY, READ ONLY cursor IS a Temp Table/While Loop combination behind the scenes. The conversion will do NOTHING to save on resources or duration.

To wit, this needs to be converted to SET BASED code if at all possible and I'm pretty sure that it's possible.

Also, if you really want to be respected in the world of SQL, particularly at your job, get into the habit of documenting the "WHY" of your code.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1335473
Posted Thursday, July 26, 2012 1:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 17, 2014 1:28 AM
Points: 64, Visits: 521
Hi

I am sending sample procedure to avoid cursors. Implement this logic in your procedures. This may help you.

declare @test_tab TABLE
(
[id1] [int] NULL,
query [varchar](50) NULL,
[result] [bit] NULL)

insert into @test_tab values(1,'select 30 - 50',Null)
insert into @test_tab values(2,'select 70 - 50',Null)
insert into @test_tab values(3,'select 20 - 20',Null)

select * from @test_tab
declare @start int=1,@vquery varchar(max),@result int

while @start <= (select COUNT(*) from @test_tab)
begin
select @vquery = query from @test_tab where id1 = @start
exec (@vquery)

set @start = @start + 1

end

Regard
Siva Kumar J
Post #1335600
Posted Thursday, July 26, 2012 7:39 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 10, 2012 7:32 AM
Points: 57, Visits: 22
Jeff and Sivaj, thank you guys for your help.

@Jeff; I understand what you said and I will keep that in mind for the next post. The reason for the WHILE LOOP is because with CURSOR it is much slower than with WHILE LOOP. The company I am contracted for advised me not to use them. I am a simple developer with not any serious developing experience. Doing this was a challenge for me but I believe I converted it correctly. Thank you.

Sivaj; Thanks for that. I appreciate it.
Post #1335846
Posted Thursday, July 26, 2012 7:51 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 19, 2014 5:16 AM
Points: 887, Visits: 1,774
morepainot (7/26/2012)
...The reason for the WHILE LOOP is because with CURSOR it is much slower than with WHILE LOOP. ...


I would love to see the tests on that statement. my question is how do you know.



For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler
Post #1335851
Posted Thursday, July 26, 2012 8:01 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 9:46 PM
Points: 86, Visits: 767
The reason for the WHILE LOOP is because with CURSOR it is much slower than with WHILE LOOP.


Who said CURSOR is slower than WHILE loops? Didn't you asked them reason for the same?

While working with cursors, there are lot of options you can set to make them more performant, check the comments above by @Jeff.

Also check this blog post where I've shown CURSORS giving good performance than WHILE LOOPS: http://sqlwithmanoj.wordpress.com/2011/02/07/avoid-cursors-or-use-them-optimally/


~manoj
sqlwithmanoj.wordpress.com
Post #1335857
Posted Thursday, July 26, 2012 8:41 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:34 PM
Points: 35,609, Visits: 32,200
morepainot (7/26/2012)
The reason for the WHILE LOOP is because with CURSOR it is much slower than with WHILE LOOP.


Like I said, that's patently and absolutely not true. The WHILE LOOP is usually what makes cursor usage so slow and I've proven that many times for companies that think other wise.

The key to performance here is to get rid of the WHILE loop. Period.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1335893
Posted Thursday, July 26, 2012 8:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:10 AM
Points: 13,230, Visits: 12,709
morepainot (7/26/2012)
Jeff and Sivaj, thank you guys for your help.

@Jeff; I understand what you said and I will keep that in mind for the next post. The reason for the WHILE LOOP is because with CURSOR it is much slower than with WHILE LOOP. The company I am contracted for advised me not to use them. I am a simple developer with not any serious developing experience. Doing this was a challenge for me but I believe I converted it correctly. Thank you.

Sivaj; Thanks for that. I appreciate it.


Agreed. This is nothing more than 5 insert statements. No need to loop through each row for this at all. If you really want to impress your contractor turn this into a set based solution.

The other thing about this procedure is that appears to be wide open for sql injection. You are receiving parameters and executing them.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1335901
Posted Thursday, July 26, 2012 8:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:10 AM
Points: 13,230, Visits: 12,709
manub22 (7/26/2012)
The reason for the WHILE LOOP is because with CURSOR it is much slower than with WHILE LOOP.


Who said CURSOR is slower than WHILE loops? Didn't you asked them reason for the same?

While working with cursors, there are lot of options you can set to make them more performant, check the comments above by @Jeff.

Also check this blog post where I've shown CURSORS giving good performance than WHILE LOOPS: http://sqlwithmanoj.wordpress.com/2011/02/07/avoid-cursors-or-use-them-optimally/


Interesting blog post Manoj. It is true you can make cursors more optimal than a while loop but why bother with them at all? In your examples there is absolutely nothing that can't be done far quicker by getting rid of the loop entirely.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1335906
Posted Thursday, July 26, 2012 8:59 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 9:46 PM
Points: 86, Visits: 767
It is true you can make cursors more optimal than a while loop but why bother with them at all? In your examples there is absolutely nothing that can't be done far quicker by getting rid of the loop entirely.


Exactly @Sean, in my example there is nothing one should use a CURSOR.
This was only to show people who believe that CURSORS are evil and use WHILE loops happily. I'm not favoring CURSORS, but just want to bust that myth.

Lot of problems can be easily done by SET based approach rather than using CURSORS. People not expert in writing SQL queries and/or not aware of new features end up using CURSORS.


~manoj
sqlwithmanoj.wordpress.com
Post #1335915
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse