While Loop Vs Cursor

Question

Post reply

While Loop Vs Cursor

Viewing 6 posts - 16 through 20 (of 20 total)

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

karthik M One Orange Chip Points: 29139 More actions · Answer 1

karthik M

One Orange Chip

Points: 29139

April 24, 2008 at 7:55 am

#806588

can i arrange a hot coffee for you ?

karthik

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

I've been out on a job. Sorry for throwing this out there and running, but I hoped it could get you started...

Jeff Moden (4/24/2008)
Barry, would you explain why you used WHERE N BETWEEN N AND somedateformula? I think it should have been WHERE N BETWEEN 1 AND somedateformula.

Thanks for picking this up Jeff, and you're right, of course. I'm pretty sure it was supposed to be "N BETWEEN 1 and ...".

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

karthik M One Orange Chip Points: 29139 More actions · Answer 3

karthik M

One Orange Chip

Points: 29139

April 25, 2008 at 12:25 am

#806982

rbarry,

Thanks for your prompt reply.

karthik

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

rbarryyoung (4/24/2008)
I've been out on a job. Sorry for throwing this out there and running, but I hoped it could get you started...
Jeff Moden (4/24/2008)
Barry, would you explain why you used WHERE N BETWEEN N AND somedateformula? I think it should have been WHERE N BETWEEN 1 AND somedateformula.
Thanks for picking this up Jeff, and you're right, of course. I'm pretty sure it was supposed to be "N BETWEEN 1 and ...".

Not a problem... knowing you, I thought you may have come up with some new bit of "rocket science" using the Tally table and was too busy to test for the same reason as you 🙂

--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)
Intro to Tally Tables and Functions

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Jeff Moden (4/25/2008)

rbarryyoung (4/24/2008)
Not a problem... knowing you, I thought you may have come up with some new bit of "rocket science" using the Tally table and was too busy to test for the same reason as you 🙂

Sadly, not this time. But your confidence is flattering 🙂

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

James Barker-429958 Grasshopper Points: 13 More actions · Answer 6

I didn't check your code, but here's what I noticed.

1. Someone mentioned "Firehose" cursor. That means Fast Forward and Read Only. Cursors, by default, are aware of changes in the table used for the SELECT. I didn't see that you were looking for changes explicitly.

2. I didn't see any select statement on the temp table within the cursor loop, so drop the index. It takes overhead to keep rebuilding it everytime you insert a record. Add it after you're done.

3. Do you really need the BEGIN/COMMIT TRANS? That alone is twice the work. Once to check for the error and then to actually insert it.