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 1234»»»

How to replace While Loop in place of Cursor Expand / Collapse
Author
Message
Posted Tuesday, December 06, 2011 5:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 06, 2013 2:19 PM
Points: 46, 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 !!
Post #1216971
Posted Tuesday, December 06, 2011 5:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:06 AM
Points: 2,404, Visits: 7,311
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.



Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1216977
Posted Tuesday, December 06, 2011 5:57 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, January 30, 2014 12:01 AM
Points: 2,013, Visits: 1,581
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.


Dev

Devendra Shirbad | DBA / Data Architect | Ex-Microsoft CSS (SQL 3T)
LinkedIn: http://www.linkedin.com/in/devendrashirbad
Post #1216979
Posted Tuesday, December 06, 2011 5:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 06, 2013 2:19 PM
Points: 46, 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 !!
Post #1216981
Posted Tuesday, December 06, 2011 6:03 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:06 AM
Points: 2,404, Visits: 7,311
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.



Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1216989
Posted Tuesday, December 06, 2011 6:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 06, 2013 2:19 PM
Points: 46, Visits: 70
Thank you Dev, could you please help me on how do I implement the same?
Post #1216990
Posted Tuesday, December 06, 2011 6:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, January 30, 2014 12:01 AM
Points: 2,013, Visits: 1,581
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.


Dev

Devendra Shirbad | DBA / Data Architect | Ex-Microsoft CSS (SQL 3T)
LinkedIn: http://www.linkedin.com/in/devendrashirbad
Post #1216997
Posted Tuesday, December 06, 2011 6:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 06, 2013 2:19 PM
Points: 46, Visits: 70
will do then..
Post #1216998
Posted Tuesday, December 06, 2011 6:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:06 AM
Points: 2,404, Visits: 7,311
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.



Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1216999
Posted Tuesday, December 06, 2011 6:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 06, 2013 2:19 PM
Points: 46, Visits: 70
Thank you, I have sent the script of my SPROC.
Post #1217020
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse