Post reply

how to run sql scripts in small batches

  • September 5, 2008 at 8:54 am

    #77084

    Hi friends,

    can any one tell me how can we run a long script or query in smaller batches. sometimes I hear my boss telling to developer buddies re-write the script in smaller batches..

    Any thoughts ?

  • September 5, 2008 at 9:03 am

    #867842

    well when you put

    GO

    on the first line of a script that defines the end of a batch.

    It will also mean that the variables leave scope etc.

    Thanks

    Chris

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • September 5, 2008 at 1:56 pm

    #868029

    Hello,

    He might be referring to creating more stored procedures that are called by a main program. Each of the called stored procedures would have a definable segment of the logic responsibility.

    For example, If the main program's job was to build a sandwich, then you might have a called stored proc that was in charge of creating a slice of bread, another for jam applicaion, another for peanut butter and then a another call to the slice of bread sproc. The idea being, that you get to use certain program segments in a repeatable fashion. Some times such code segments lend themselves to UDF constructs. You might create a UDF that looks up a customer name when given a customer number etc.

    Just food for thought.

    Regards,

  • September 7, 2008 at 11:23 am

    #868239

    Joseph (9/5/2008)

    Hi friends,

    can any one tell me how can we run a long script or query in smaller batches. sometimes I hear my boss telling to developer buddies re-write the script in smaller batches..

    Any thoughts ?

    Basically, it's a loop that processes the TOP # rows that meet a certain criteria and continues until there's no rows that meet the criteria. For example, to delete a huge number of rows from an even larger table, you might want to delete only 25,000 rows at a time to keep from locking the table for extended periods. Some folks will actually put a delay in the loop to give the system time to "breath" on other tasks.

    To be sure, the loop does NOT process one row at a time... it processes, say, 25,000 rows at a time.

    --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 4 posts - 1 through 3 (of 3 total)

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