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

Replacing Cursors and While Loops Expand / Collapse
Author
Message
Posted Wednesday, June 29, 2005 4:16 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 9, 2009 1:35 PM
Points: 60, Visits: 12
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/cherring/replacingcursorsandwhileloops.asp


Post #195535
Posted Monday, July 11, 2005 1:09 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 4:53 AM
Points: 229, Visits: 166

Try this code. you might save remaining 12 Minutes also..

UPDATE
 B
SET
 b.id = A.id
FROM
 dbo.many_tbl A inner join dbo.OutpUt_tbl B
 ON A.id = B.ID

 

Jeswanth



--------------------------------
Post #199191
Posted Monday, July 11, 2005 3:16 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, November 10, 2011 5:55 AM
Points: 293, Visits: 34

I can't see the purpose of the ORDER BY clause.  If you were to remove this from your select statement, you may find that you improve the performance.  It may also be the case that the outer loop has an ORDER BY clause that can also be removed.

Profile the procedure to determine what indexes are being used on the tables.  Ensure that the indexes in use are the most efficient. You may be able to improve the performance yet again by creating covering indexes.




Stephen Bailey
Post #199217
Posted Monday, July 11, 2005 5:53 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, December 14, 2011 12:03 PM
Points: 613, Visits: 119

If you don't believe this example take a look at the example I posted which Noeld and Remi helped me convert:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=197948

This went from 2 hours to 6:46 to 0:26 in the final iteration!!





I reject your reality and substitute one of my own. - Adam Savage-Mythbuster
Post #199254
Posted Monday, July 11, 2005 7:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 27, 2011 5:04 AM
Points: 20, Visits: 2

Recently I have had to create an admin task that needs to be run on our 24gb database each day.  Unfortunately due to our company security I cannot show the DDL but will do my best to explain...

In this task (50000 lines of TSQL) I have had to use several cursors to complete the task.  This task is broken down into several smaller or sub tasks to perform the entire operation.

Though I admit I have not had the chance to fully analyze the task as yet to identify scans and the like (which I will get to shortly) however I wish to try and rid myself of the cursors first.

In one 'sub' task I managed to do this by converting a cursor into a simple insert statement:

Insert Into dbo.tbl
Select col1, col2, col3
From dbo.workingtbl

This managed to cut time down from 11 minutes to 3.  Working with approx 500,000 rows in the sql above.

 

The remaining cursors are becoming a little hard to work our.  In the cursor I open approximately 20 variables which are used throughout the loop..

Declare #x cursor for
select col1, col2... col20
from dbo.table
order by col1

fetch next from #x into @var1, @var2... @var20

while.....  (you all know the rest)


In the while loop above I perform close to 100 calculations, 5 to 10 updates and inserts etc....

With this many calculations and updates/inserts could anyone suggest a better method of this loop without using a cursor?  Or perhaps this is one of those isolated cases where a cursor is best?

Either way, any suggestions would be great!

Post #199306
Posted Monday, July 11, 2005 7:59 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Monday, September 22, 2014 6:13 AM
Points: 20,578, Visits: 9,618
It would be best if you started a new thread for this question. You will get more attention to it and you'll be able to provide more details.

Could you provide the table definition (even if you change the names). Some sample data and the expected outputs. I'm thinking at the moment that you could do a first select statement that would do the 100 calculations and insert that data in a temp table. Then you could do your updates/inserts using that temp table. It should run 10/50 times faster that way.
Post #199312
Posted Monday, July 11, 2005 8:12 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135

Great article.  A lot of people make the mistake of trying to use SQL as a procedural language instead of a declarative language.  It's hard for some to think in terms of SQL set-based statements as opposed to iterative looping and cursors.

I'd estimate that cursors could be eliminated and replaced with set-based SQL statements 99.9999% of the time that they are used.

Post #199344
Posted Monday, July 11, 2005 8:23 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135

In addition to what was already stated about creating a temp table to hold intermediate results, if your calculations are conditional using IF..ELSE constructs, you might look at converting them to CASE expressions and/or modifying your WHERE clauses.

Without seeing your DDL it's impossible to get much more detailed than that.

 

Post #199358
Posted Monday, July 11, 2005 8:49 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 2:44 PM
Points: 3,475, Visits: 583

Hi,

Good Article!

In my opinion cursor usage should depend on "How Often". Cursors provide an easy programming solution when the extensive row processing should be done. If something should be done only a couple of times like in one time data migration tasks, I ususally opt for cursors. If something has to run every day and takes a lot of time than the set approach should be researched and maybe some database architecture changes including de-normalizing the database should be done.

Yelena




Regards,
Yelena Varshal

Post #199370
Posted Monday, July 11, 2005 10:18 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 9, 2009 1:35 PM
Points: 60, Visits: 12
Stephen,

In my actual code the data needed to be order in the pivoted result set. If one doesn't care about the order then your correct, the order by is not needed.

Clint



Post #199429
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse