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


There Must Be 15 Ways To Lose Your Cursors... part 1, Introduction


There Must Be 15 Ways To Lose Your Cursors... part 1, Introduction

Author
Message
Peter Hansen
Peter Hansen
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 22
I've read a number of "get rid of cursors" articles. None of them have addressed the issues that I have faced (and solved) with cursors. Perhaps this series will be different, I would certainly love to pick up some new ways to approach the sort of complex problems that appear in the real world.

I recently completed a very complex bit of code that has three nested cursors. The horror! The fact is that it works well because I have limited the number of rows (the outer cursor processes a few hundred rows, the inner cursors a handful). The specific nature of the problem I solved had to do with complex business logic that needed to be applied to each row, with a series of cascading rules. One other major issue I have is that I believe that code needs to be easy to understand. One issue I have with set-based coding is that it tends to obfuscate the logic when things get to be complex. In some cases, cursors have a readability advantage.

I come from a procedural background, that certainly colors my view of the world. But I am wary of those who proclaim that any given technique is universally bad. Almost 40 years or writing code in various forms has taught me that there are mutiple approaches to any given problem, each with trade-offs. The real skill of programming is to pick the best approach. At times, cursors are that best approach.

It must be mentioned that my environment is Sybase (and an old version at that!), not SQL Server. Living in this relatively primitive environment I can't take advantage of the latest features. We often have to make do with what we have.

Peter Hansen
Matt Whitfield
Matt Whitfield
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1197 Visits: 719
Peter Hansen (4/14/2009)
I've read a number of "get rid of cursors" articles. None of them have addressed the issues that I have faced (and solved) with cursors. Perhaps this series will be different, I would certainly love to pick up some new ways to approach the sort of complex problems that appear in the real world.

I recently completed a very complex bit of code that has three nested cursors. The horror! The fact is that it works well because I have limited the number of rows (the outer cursor processes a few hundred rows, the inner cursors a handful). The specific nature of the problem I solved had to do with complex business logic that needed to be applied to each row, with a series of cascading rules. One other major issue I have is that I believe that code needs to be easy to understand. One issue I have with set-based coding is that it tends to obfuscate the logic when things get to be complex. In some cases, cursors have a readability advantage.

I come from a procedural background, that certainly colors my view of the world. But I am wary of those who proclaim that any given technique is universally bad. Almost 40 years or writing code in various forms has taught me that there are mutiple approaches to any given problem, each with trade-offs. The real skill of programming is to pick the best approach. At times, cursors are that best approach.

It must be mentioned that my environment is Sybase (and an old version at that!), not SQL Server. Living in this relatively primitive environment I can't take advantage of the latest features. We often have to make do with what we have.

Peter Hansen

This sounds familiar! Smile We took that as a challenge last time, and we came up with a solution that was set based and increased performance. And we actually found some issues logically with the original implementation too.

Gotta love a challenge Smile

Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
noxidjkram
noxidjkram
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 82
Another vote for a well written introduction.

For all those experienced and insulted DBAs out there, please remember the up-and-comings like myself who really appreciate an introduction to the subject wherever possible.

I, like many others I suspect, are expected to use SQL as part of their job but have never been offered/given training - i've learned what I know from a DBA and a Systems Analyst that I work with over a period of nearly 2 years now, and I know my knowledge is shockingly bad (i have no theoretical knowledge at all).

In summary - Thanks for the article, and I'm looking forward to reading part 2...

SQL Novice - Here to learn.
Anipaul
Anipaul
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8073 Visits: 1407
Very nice article and really well explained. I will also enjoy this series....



VinicioAizpurua
VinicioAizpurua
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 115
I think Brad took it personal!!! This article is an opinion and it is up to us to discuss and bring our comments in how to improve the a way to work with cursors. I believe that sometimes cursors might be useful, but after 30 years of programming in different languages and DB, there is always a better way to do it. For instance I was checking a process that was updating some records on a table, and noticed that the process was taken long time to execute, then I check on the code and found that there was a cursor but the problem was not in the cursor itself but in the way the cursor was used; it was updating the table record by record, so I took the result to a temp table and performed a massive update on the final table. What a surprise a process that it was taken more that 30 min. was reduced to 2-3 minutes and even less. In other hand, sometime programmers are assign to do certain taks and they tend to have "TEMPLATES" in their head and are not capable to think out of the box, that is why if you team lack of programming standards and code QA/QC and/or DBA team that approves the code the result is obvious. Remember we work base on FACTS and Barry have an opinion that we have to be intelligent enough (Do not take it personal) to contribute by providing a solution or giving you opinion that stands against the article. Finally I agreed with gautamsheth200 by doing a "Select count(*) From master.sys.columns C1 CROSS JOIN master.sys.columns C2" you have the same result, but once again you have to analyze the situation and see if you are able to improve the code, by the way from 23 to 1 second by the solution presented.
Best regards to all
Vinicio Aizpurua
Systems Analyst/Developer/DBA
rot-717018
rot-717018
Old Hand
Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)

Group: General Forum Members
Points: 301 Visits: 440
Good introduction! Looking forward to reading part 2 (+1).

And totally agree with Kev. I only want to add that nobody is asking anybody to fire their cursors if they prefer to keep them ...

And to avoid maybe unnecessary posts ... yes the cursor should be closed and deallocated and select count(*) is not the solution to avoid cursors in the example, although it would suit the requirements here.
GabyYYZ
GabyYYZ
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1129 Visits: 2336
You're a brave man RBarryYoung. At least the comments here have been kinder than with the article I wrote (link omitted on purpose, still shellshocked, LOL)

Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein

Matt Whitfield
Matt Whitfield
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1197 Visits: 719
Gaby

I have to admit - I wasn't too kind on your original article. BUT - it did spawn what was possibly the most interesting thread ever on SSC. You should be proud! :-D

Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
Manie Verster
Manie Verster
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1801 Visits: 1022
Let me just make myself clear. I am not pro-cursor or pro-setbased or pro-whatever, I am pro-SQL Server and I don't have a set way of doing things. I always look at what would be the best way of doing a job. I just say that there is not one subject in SQL that is good or bad, it is up to you to make it good.

Barry, when are you going to give us your comments on what is being said here?

:-PManie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
longobardia
longobardia
SSC Veteran
SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)

Group: General Forum Members
Points: 291 Visits: 182
There is really nothing wrong with using cursors. If the application doesn't call for performance, YES cursors are quite easy to implement and everyone can read and understand them.

I myself have used them for ages now and found that they are quite useful at times. However, now that I am in a situation where my application is in need of the full potential of the SQL server, I cannot afford to use them.

The example posted, is just for you to measure the time that the query takes to run in your environment and to show what a resource hug a cursor could be. In my situation, the query ran for 20 seconds flat from the t-sql example provided.

After looking at the example cursor, I can see that this will generate a huge amount of rows with the crossjoin.

After recoding with a few select statement, I was able to make it run in less than 1 second.

That goes to show you that cursors are very costly to run.

Now, I am no SQL Genius but I was able to formulate the following query based on the example:



select max(k.RowNum)
From (
select ROW_NUMBER() over (order by s.dta desc) as 'RowNum'
from (
Select 1 as Dta
From master.sys.columns c1
Cross Join master.sys.columns c2
) as s
) as k




Please feel free to comment or correct if wrong or wrong approach taken.

Al;-)
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