SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to replace While Loop in place of Cursor


How to replace While Loop in place of Cursor

Author
Message
MSBI Learner
MSBI Learner
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 70
Hi – I am using a Stored Procedure from where our SSRS report runs but now our report takes really huge time and we had to work on tuning of SPROC.

We are actually using CURSOR in our SPROC because we had to pull the calculated time fields

DECLARE RM_CURSOR CURSOR FOR
SELECT D.id,D.PeriodDesc,D.StartDate,D.EndDate
FROM #DateRanges D
ORDER BY D.id

OPEN RM_CURSOR
FETCH NEXT FROM RM_CURSOR INTO @id,@PeriodDesc,@StartDate,@EndDate

WHILE @@FETCH_STATUS = 0
BEGIN

**
**

FETCH NEXT FROM RM_CURSOR INTO @id,@PeriodDesc,@StartDate,@EndDate
END

CLOSE RM_CURSOR
DEALLOCATE RM_CURSOR

I read in most of the blogs that CURSOR will degrade the performance, and WHILE LOOP can be replaced instead of CURSORS.

Can someone please help me how can I use WHILE LOOP in above scenario?

Thanks !!
Cadavre
Cadavre
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3872 Visits: 8472
CURSORS and WHILE LOOPS are both generally bad, if you're tuning your query then maybe you should look at a more set-based approach?

If you provide readily consumable sample data, DDL scripts, the current code in your sproc and your expected result-set based on your sample data then I'm sure there will be a way to make your code perform much much better.

Please read this article about the best way to provide us with readily consumable sample data and DDL scripts, which will allow the unpaid volunteers of this site to provide you with working, tested code that fulfils your requirements.

Thanks.


Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
Dev
Dev
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3486 Visits: 1602
DECLARE RM_CURSOR CURSOR FOR
SELECT D.id,D.PeriodDesc,D.StartDate,D.EndDate
FROM #DateRanges D
ORDER BY D.id


OPEN RM_CURSOR
FETCH NEXT FROM RM_CURSOR INTO @id,@PeriodDesc,@StartDate,@EndDate

WHILE @@FETCH_STATUS = 0
BEGIN


Your looping logic is based on Cursor fetch status. Declare a CounterVariable & get the number of the rows for your cursor query. Compare this CounterVariable in your loop & your are good to remove cursor.
MSBI Learner
MSBI Learner
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 70
Yes – my motto is to improve the performance of my Stored Procedure. Yes – I can provide you the required information, would you mind to provide me your personal email ID as I don’t want to share the script in the thread due to some security issues.

Hope you don’t mind.

Thanks !!
Cadavre
Cadavre
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3872 Visits: 8472
MSBI Learner (12/6/2011)
Yes – my motto is to improve the performance of my Stored Procedure. Yes – I can provide you the required information, would you mind to provide me your personal email ID as I don’t want to share the script in the thread due to some security issues.

Hope you don’t mind.

Thanks !!


Sorry, no. Just obfuscate the data, so that it's similar to what you're using but not the same thus not violating any data protection laws.

For example, I use variations of the below script when I'm testing for performance
--Standard TestEnvironment of 1,000,000 rows of random-ish data
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment
END

--1,000,000 Random rows of data
SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,
RAND(CHECKSUM(NEWID())) * 30000 + CAST('1945' AS DATETIME) AS randomDate,
ABS(CHECKSUM(NEWID())) AS randomBigInt,
(ABS(CHECKSUM(NEWID())) % 100) + 1 AS randomSmallInt,
RAND(CHECKSUM(NEWID())) * 100 AS randomSmallDec,
RAND(CHECKSUM(NEWID())) AS randomTinyDec,
RAND(CHECKSUM(NEWID())) * 100000 AS randomBigDec,
CONVERT(VARCHAR(6),CONVERT(MONEY,RAND(CHECKSUM(NEWID())) * 100),0) AS randomMoney
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3



It creates 1,000,000 rows of pseudo-random data of different data-types.

Can you do something similar for the set-up of your issue? I know it means taking some time out to provide the scripts required, but I promise that there are some extremely talented people that frequent these forums so you will end up with a much more efficient query.


Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
MSBI Learner
MSBI Learner
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 70
Thank you Dev, could you please help me on how do I implement the same?
Dev
Dev
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3486 Visits: 1602
MSBI Learner (12/6/2011)
Thank you Dev, could you please help me on how do I implement the same?


If help means giving you a running piece of code then I can't help you. Please try to implement what I have already suggested to you. It's not very difficult so don't miss the opportunity to learn it.
MSBI Learner
MSBI Learner
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 70
:-) will do then..
Cadavre
Cadavre
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3872 Visits: 8472
I'm going to re-iterate what I said before. . .

CURSORS and WHILE LOOPS are both generally bad, if you're tuning your query then maybe you should look at a more set-based approach?

If you use either, the chances are fairly good that you are not using the most efficient algorithm to perform your task.

If you provide readily consumable obfuscated sample data, DDL scripts, the current code in your sproc and your expected result-set based on your sample data then I'm sure there will be a way to make your code perform much much better. In fact, if the current code in your sproc is "secret", just provide the expected result based on your obfuscated sample data.

Please read this article about the best way to provide us with readily consumable sample data and DDL scripts, which will allow the unpaid volunteers of this site to provide you with working, tested code that fulfils your requirements.


Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
MSBI Learner
MSBI Learner
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 70
Thank you, I have sent the script of my SPROC.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search