Post reply

Variable quest

  • 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.

     

  • September 8, 2006 at 2:41 pm

    #659462

    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

  • September 8, 2006 at 4:50 pm

    #659485

    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)

  • September 9, 2006 at 1:30 pm

    #659508

    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.

  • September 9, 2006 at 1:44 pm

    #659509

    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)

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

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