How to execute stored proc iin a loop for a date range

  • I have a stored procedure that needs to be called to update a table inside for a large date range (millions of records), but I wanted this stored procedure

    to be looped through some kind of batches mechanism so that not too many records being update at a time. What is the best way for me to call this

    stored proc in batches? thanks a lot.

    declare @startdate datetime, @enddate datetime;

    select @startdate=min(datefield), @enddate = max(datefield)

    from table;

    execute sproc_updatesomething @startdate, @enddate;

  • sqlblue (10/17/2013)


    I have a stored procedure that needs to be called to update a table inside for a large date range (millions of records), but I wanted this stored procedure

    to be looped through some kind of batches mechanism so that not too many records being update at a time. What is the best way for me to call this

    stored proc in batches? thanks a lot.

    declare @startdate datetime, @enddate datetime;

    select @startdate=min(datefield), @enddate = max(datefield)

    from table;

    execute sproc_updatesomething @startdate, @enddate;

    It is pretty unclear what you are doing. What do you mean by call the proc in batches?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (10/17/2013)


    sqlblue (10/17/2013)


    I have a stored procedure that needs to be called to update a table inside for a large date range (millions of records), but I wanted this stored procedure

    to be looped through some kind of batches mechanism so that not too many records being update at a time. What is the best way for me to call this

    stored proc in batches? thanks a lot.

    declare @startdate datetime, @enddate datetime;

    select @startdate=min(datefield), @enddate = max(datefield)

    from table;

    execute sproc_updatesomething @startdate, @enddate;

    It is pretty unclear what you are doing. What do you mean by call the proc in batches?

    I agree with Sean in that the request isn't very clear. If you add parameters to a procedure and then pass in the MIN and MAX of a single date field, it'll update the whole table. The only point in including the values in the first place would be to try and force it to use an index.

    BTW, if you mean that you want to fire multiple update statements for blocks of dates and put that inside a loop to process N rows at a time, you're going to end up updating the whole table anyway, but taking multiple steps to do it. Because the look will run one iteration after the other, you'll be doing the same net amount of work, but multiple statements would just seem slower than a single, well-tuned update statement. There must be something I'm missing here. Could you please expand on what you're trying to do?

  • Thanks for responding. I need to execute the stored procedure in batches either through a while loop or some kind of table with identity column, but I don't know what is the best or most efficient way to do it. I cannot modify the stored proc, I can only call it with a date range given. The date range is big, and I don't want to update all the records in that one date range. So I am asking is there a way for me to execute the stored procedure in batches. Just like you would with an update or insert statement, but rather it is a stored procedure, but with the stored proc, I have to somehow link the batches to the stored procedure in order to update correctly within the date range given.

    This is just an example of what I am trying to do

    while @mindate (or @minid) < @maxdate (or @maxId)

    begin

    exec sproc_updatesomething @minid

    set @minid = @mind + 1

    end

    something like that. hope this explains what I am trying to do. Maybe I need to split the date into week or month or something.

  • sqlblue (10/17/2013)


    Thanks for responding. I need to execute the stored procedure in batches either through a while loop or some kind of table with identity column, but I don't know what is the best or most efficient way to do it. I cannot modify the stored proc, I can only call it with a date range given. The date range is big, and I don't want to update all the records in that one date range. So I am asking is there a way for me to execute the stored procedure in batches. Just like you would with an update or insert statement, but rather it is a stored procedure, but with the stored proc, I have to somehow link the batches to the stored procedure in order to update correctly within the date range given.

    This is just an example of what I am trying to do

    while @mindate (or @minid) < @maxdate (or @maxId)

    begin

    We can't even pretend to know what would be the most efficient. We have no idea what the tables, the proc, or the requirements for this are. About all I can offer is you will need to figure out what range of dates will be acceptable and do some looping around that.

    Is this a one time thing? If so, I would recommend taking the guts of the stored proc and rolling the whole thing into a single script that can be controlled more easily. If this is something you need to repeat you need to provide a LOT more information and very likely a major overhaul of the whole thing.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • sqlblue (10/17/2013)


    What is the best way for me to call this

    stored proc in batches?

    Below is an example of what I think you are trying to do. This proc will keep calling itself until it's done. I am using an arbitrary surrogate key to break the job into batches; the proc will execute (<#of rows to update> /@row_end) times.

    Here is some sample data:

    -- (1) Let's create some sample data

    USE tempdb

    GO

    IF OBJECT_ID('tempdb..#sometable') IS NOT NULL DROP TABLE #sometable;

    CREATE TABLE #sometable(some_id int primary key, some_value varchar(40), some_date date);

    WITH iTally(n) AS

    (SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY (SELECT (0)))

    FROM sys.all_columns a CROSS JOIN sys.all_columns b)

    INSERT INTO #sometable

    SELECT n, 'old value', getdate()-(2500+floor(2500 * RAND(convert(varbinary, newid()))))

    FROM iTally

    GO

    --BEFORE

    SELECT * FROM #sometable

    ORDER BY some_id

    GO

    Here is a recursive stored proc with a couple examples:

    -- (2) The recursive update stored proc

    CREATE PROC someproc(@row_start int=0, @row_end int=10000, @new_value varchar(100)='new value')

    AS

    SET NOCOUNT ON;

    WITH surrogate(s_key) AS

    (SELECT ROW_NUMBER() OVER (ORDER BY some_id)

    FROM #sometable)

    UPDATE #sometable

    SET some_value=@new_value

    WHERE some_id>@row_start AND some_id<=@row_start+@row_end;

    SELECT @row_start=@row_start+@row_end;

    IF @row_start<(SELECT COUNT(*) FROM #sometable)

    EXEC someproc @row_start,@row_end,@new_value;

    GO

    --using the defaults

    EXEC someproc

    --50,000 rows

    EXEC someproc 0,50000,'blah, blah'

    Perhaps this will help.

    P.S. I came up with this in a few minutes during lunch and don't don't know if this is the best way to accomplish this or not (comments, criticism welcome).

    EDIT: my stored proc had a couple issues. All fixed.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Thanks a lot Sean, Alan for responding. Especially Alan for taking your lunch time to write the code. I thought I provided enough information, but I guess not, sorry.

    Anyway, I think I will write a loop to have the big date range break into week/month range and insert into a table (like below), and then use the Identity column in that table to loop through to exec the stored procedure, and that should work.

    table

    ID (identity column) fromDate toDate

    1 01/01/2001 01/31/2001

    2 01/02/2001 02/28/2001

Viewing 7 posts - 1 through 6 (of 6 total)

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