Eliminating Cursors

Question

Eliminating Cursors

Viewing 15 posts - 211 through 225 (of 296 total)

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Pierre - you might care to take a look at this article. Neither one of those is going to be particularly efficient, since one is a WHILE loop, and one is a CURSOR loop. I'm also not quite sure what you get out of those, since you're not using or storing those values (so the "running" part of this doesn't really amount to anything since you don't have it available to you at the end of either script).

I'm sure you were simplifying, but what you have now doesn't give you a whole lot....

This is an alternative which should give you some rather subtantial perf gains....:

http://www.sqlservercentral.com/articles/Advanced+Querying/61716/[/url]

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

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

Heh... cool... I finally beat Mr. Miller at a response... 😛

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Careful - I was out getting coffee...:)

besides - it seems I spend a fair amount of time posting links to your articles anyway...:D

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Wilfred van Dijk (6/16/2008)
I agree with Bob. Why write some unreadable code in order to avoid cursors? To my opinion, cursors are easy to implement, very straightforward and the extra cost of performance is nothing compared to the whole batchprocess in which it's being used.

Like English, French and VB, Set-based SQL is only "unreadable" if you are not proficient in it. And if you are not proficient in it, then you are not professionaly qualified to be doing SQL Server development. The solution to that of course, is to start using it as much as possible.

And for the record, as someone who works in both the procedural/OO development and SQL/Set-oriented development, I find the later to be far, far easier to read.

[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]

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

I strongly encourage everyone who wishes to do so to write "more readable/maintainable" code that involves cursors - and my email address is kgboles@earthlink.net. Keep that handy so you can contact me for some tuning work when your server starts to crawl. 😎 If you are LUCKY, then your product/website/client load won't grow much in which case you will be fine. Of course that also means that you aren't growing your business too. :hehe:

Jeff, I have referenced the Running Totals thread at least 10 times now on various MS forums, including the MVP private one. Some really great stuff everyone did there!!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Pierre Boucher Valued Member Points: 68 More actions · Answer 6

Just for the records, I was not writing any production code or trying to cumulate anything. Just testing what Mr. Ali said about the performance of memory table vs cursor by using his own code to start with and using some of the enhancement technique posted by others.

So, if you see my resume at your company, I will deny ever writing that code :ermm:

Pierre Boucher

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 7

Pierre Boucher (6/16/2008)
So, if you see my resume at your company, I will deny ever writing that code :ermm:

Pierre... I think I like your attitude... alot!:)

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

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 8

TheSQLGuru (6/16/2008)
Jeff, I have referenced the Running Totals thread at least 10 times now on various MS forums, including the MVP private one.

Thank you for the awesome honor, Kevin.

Some really great stuff everyone did there!!

The discussions that follow some of these articles are totally awesome. Lots of good folks had a lot of great ideas which made the article pale in comparison...

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

Jeff Moden (6/16/2008)
Pierre Boucher (6/16/2008)
So, if you see my resume at your company, I will deny ever writing that code :ermm:
Pierre... I think I like your attitude... alot!:)

Agreed!

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Parwej Pathan Newbie Points: 1 More actions · Answer 10

Hi,

I replaced the nested cursors used in one of the stored procs with while loops and temp tables logic and found that performance of cursor is better. It is taking 3 sec more than that of cursor logic. :crying: I am using sql 2000.

Sergiy SSC Guru Points: 110208 More actions · Answer 11

Kevin Rathgeber (6/16/2008)
Just a quick comment about the table based.
Move @loop_counter out of the while into an IF statement outside the loop. Though its minimal, there is no point in running the @loop_counter > 0 comparison on every record when the variables value never changes and only needs to be checked once.
Should look like this:
IF @loop_counter > 0
BEGIN
WHILE @item_category_counter <= @loop_counter
BEGIN

Kevin, why you need that IF at all?:hehe:

There is an assignment right before the loop:

SET @item_category_counter = 1

If @loop_counter = 0 then @item_category_counter > @loop_counter and loop never got executed!:w00t:

Isn't it obvious?

😛

_____________
Code for TallyGenerator

Kevin Rathgeber SSC Veteran Points: 270 More actions · Answer 12

Kevin, why you need that IF at all?
There is an assignment right before the loop:
SET @item_category_counter = 1
If @loop_counter = 0 then @item_category_counter > @loop_counter and loop never got executed!
Isn't it obvious?

Oh sheesh, totally missed that. Thanks for pointing that out.

Obvious? Obvious is relative to the amount of sleep one had the night before. 😀

Sergiy SSC Guru Points: 110208 More actions · Answer 13

1st rule of programming:

There is no program which cannot be shortened by 1 operator.

🙂

_____________
Code for TallyGenerator

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 14

Sergiy (6/17/2008)
1st rule of programming:
There is no program which cannot be shortened by 1 operator.
🙂

Corollary to rule #1: Try not to be that operator....:D

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Kevin Rathgeber SSC Veteran Points: 270 More actions · Answer 15

Sergiy (6/17/2008)
1st rule of programming:
There is no program which cannot be shortened by 1 operator.
🙂

Got to love programming where the better of the two epiphanies was not reducing the number of operations by 300,000, but by reducing it by 1.