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

There Must Be 15 Ways To Lose Your Cursors... part 1, Introduction Expand / Collapse
Author
Message
Posted Tuesday, April 14, 2009 5:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 25, 2010 9:37 AM
Points: 3, 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
Post #696439
Posted Tuesday, April 14, 2009 5:39 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 22, 2013 11:59 AM
Points: 509, 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! :) 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 :)


Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
Post #696441
Posted Tuesday, April 14, 2009 5:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 3:28 AM
Points: 13, Visits: 74
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.
Post #696443
Posted Tuesday, April 14, 2009 5:48 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, March 20, 2014 7:32 AM
Points: 5,191, Visits: 1,368
Very nice article and really well explained. I will also enjoy this series....


Post #696445
Posted Tuesday, April 14, 2009 6:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 20, 2012 1:38 PM
Points: 40, 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
Post #696483
Posted Tuesday, April 14, 2009 6:51 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 04, 2014 8:53 AM
Points: 80, Visits: 249
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.
Post #696489
Posted Tuesday, April 14, 2009 7:00 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: Wednesday, April 09, 2014 12:22 PM
Points: 806, Visits: 2,096
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
Post #696496
Posted Tuesday, April 14, 2009 7:09 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 22, 2013 11:59 AM
Points: 509, 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!


Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
Post #696500
Posted Tuesday, April 14, 2009 7:25 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, March 28, 2014 5:06 AM
Points: 1,204, Visits: 915
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?


Manie 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)
Post #696506
Posted Tuesday, April 14, 2009 7:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, November 07, 2013 10:13 AM
Points: 264, 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
Post #696513
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse