Variable quest

Question

Post reply

Variable quest

Mindy Hreczuck

SSCommitted

Points: 1796
More actions
September 8, 2006 at 2:37 pm

#172479

I have a currently functioning script that updates records based on media they belong to, however since the number of records per media varies greatly, I'd like to set a variable to control how many media to update with each pass, to keep the total records down low, say equal or under 10,000 at a time. I haven't quite figured out how to set the variable up.
Example -
MediaId ... TotalRecs (count(id))
--------------------------
Media_09 ... 4011
Media_08 ... 5762
Media_07 ... 2845
Media_06 ... 4869
Media_05 ... 1227
Media_04 ... 3541
So, I currently have @start (Media_09) and @end (Media_04), and @last = @start - 5. I'd like to make the "5" the variable so if the total for @start and subsequent media hits 10,000, stop and set the variable, say @range, to that delta. The first pass in this case would set @last = @start - 2, then the second would set @last = @start - 3, etc.

Viewing 5 posts - 1 through 5 (of 5 total)

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Can you post your script? Are you just trying to limit the number of rows you update at one time?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

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

The other question would be... how many rows are you updating? It should only take about 3.5 seconds to update 20k rows.

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

Mindy Hreczuck SSCommitted Points: 1796 More actions · Answer 3

We have a second process outside of SQL that the records will go through that passing more than 10K records to can interfere with so I can't do more than that at this time. The total update is probably several hundred thousand.

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

In that case, why not just update by what's in the MediaID column? Load the unique media IDs into a temp table (with an autonumbering IDENTITY column) and step through them doing one update per media ID. Then, you don't have to mess around with all the @start & @end stuff.

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