Eliminating Cursors

Question

Post reply

Eliminating Cursors

Viewing 15 posts - 226 through 240 (of 296 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

Matt Miller (6/17/2008)
Corollary to rule #1: Try not to be that operator....:D

Quality of some programs makes you wish to be the one.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

Sergiy (6/17/2008)
Matt Miller (6/17/2008)
Corollary to rule #1: Try not to be that operator....:D
Quality of some programs makes you wish to be the one.

Kinda funny you should say that... I'm going through a bit of that at work right now... folks are talking about when to sacrifice readability for performance for maintainability, etc and vice versa for each as well as when it's ok to bypass the QA process (its never ok in my book)... what they're really doing is unneccesarily compromising the quality of code and maybe even their personal integrity as a professional developer or manager. That bad part is, all that stuff is mostly pretty easy... shouldn't ever have to compromise on any of it even in the face of accelerated schedules. And, every time they do make such a compromise, something bad happens... code breaks in production, performance problem, or "latent" defects start showing up.

Funny part is if they spent less time whining about all of that, they'd have more time to preserve the quality of the code... 😛 I recently spent an hour with one developer (just to see how long it would take) talking about why he needed to document his little ol' hundred lines of code... when I finally said "Look, document it or the code doesn't move... you'll be late", it took him less than 5 minutes to properly and correctly document it according to the standards.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

JStiney Say Hey Kid Points: 671 More actions · Answer 3

Reviewing Jeff’s test results again, it seems that the CLR option ran even faster that the set based solutions, before the helper indexes were added.

Does this mean that CLR changes RBAR to just plain RBR?

We are still on SQL 2000 so I have not done anything with CLR, but from the reading I’ve done my impression was that CLR was not faster than TSQL when doing a lot of disk IO, so I was surprised by the results. I’m also surprised that the fastest solution did not generate any subsequent discussion. Are we all missing a good solution to the cursor problem, here?

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

Are we all missing a good solution to the cursor problem, here?

Nope... with the correct indexing, the CLR looses in speed and maintainability. And this is a pretty rare case... CLRs don't normally beat properly written set based SQL. If they did, I wouldn't be using a store bought database... I'd write my own like I did back in the early 90's. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

John Paul-702936 SSCrazy Points: 2182 More actions · Answer 5

Hi Sir ,

I have gone through the Topic .."elimnating Cursors using While loop ".. that's great ..

Can you expand the Explanation please..

I have one store procedure which uses Cursor .. i want to replace this Cursor .....

John Paul-702936 SSCrazy Points: 2182 More actions · Answer 6

Hi Sir ,

I have gone through the Topic .."elimnating Cursors using While loop ".. that's great ..

Can you expand the Explanation please..

I have one store procedure which uses Cursor .. i want to replace this Cursor .....

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

praveenvelumula (5/15/2009)
Hi Sir ,
I have gone through the Topic .."elimnating Cursors using While loop ".. that's great ..
Can you expand the Explanation please..
I have one store procedure which uses Cursor .. i want to replace this Cursor .....

Excellent. Please post the code and we will advise you on it.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Ron Cicotte SSC Veteran Points: 274 More actions · Answer 8

If you went through the thread you should have noticed that the "while loop" doesn't buy you much if anything. The objective is to replace the cursor with set based queries. This requires that you analyze the code and group the operations of the cursor into a set of insert, update and delete operations on the affected records. For example:

If you have a cursor that is processing a bunch of purchase orders you would first get a set of purchase order ID's for the orders being processed and save them in a temp table (or better yet a table variable assuming that the set is not too large). Then you would update the associated records by joining on this table, the purchase order table and other related tables using the po ID as the key. Set based queries are orders of magnitude faster than cursors. There are very few (but there are some) situations where the cursor cannot be replaced with a more efficient set based solution.

PS. After further review I noticed that the discussion indicates the test showed cursor performance better than set based queries only when the proper indexing had not been performed. Indexing is a critical part of any performance analysis. If your not indexing properly then full table scans are common which is really not much different than using a cursor. If your not sure what to index just look at your WHERE clause. Your ID's should generally be clustered primary keys in the primary table and non-clustored index in the associated table.

Ron Cicotte
ron.cicotte@gmail.com
Data Transformations, LLC

John Paul-702936 SSCrazy Points: 2182 More actions · Answer 9

John Paul-702936

SSCrazy

Points: 2182

May 15, 2009 at 1:13 pm

#995611

Here is the Code .. Please Advise ...

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

Can you provide the CREATE TABLE DDLs for the referenced tables also?

thnx

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

Actually, even more that that, I need the listing of the "spDBSys_GetTableRowKey_sproc" stored procedure.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

John Paul-702936 SSCrazy Points: 2182 More actions · Answer 12

John Paul-702936

SSCrazy

Points: 2182

May 15, 2009 at 1:35 pm

#995634

Script File:::

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 13

Praveen,

What version of SQL Server are you using? 2k, 2k5, 2k8?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

John Paul-702936 SSCrazy Points: 2182 More actions · Answer 14

Microsoft SQL Server 2000 - Enterprise Edition on Windows NT -Service Pack 1

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 15

praveenvelumula (5/15/2009)
Microsoft SQL Server 2000 - Enterprise Edition on Windows NT -Service Pack 1

Thanks Praveen.

The code has a sequence table in it instead of using identities... the way the code to get the next ID is written, I'm surprised you're not getting massive amounts of deadlocks especially since that code is called within a transaction.

Obviously, I'm looking at what can be done with your code but I do have to tell you... it's not my intent to rewrite a 300+ line stored procedure for you. My intent is to show you what can be done to replace one of the inner cursors and then let you have at it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)