October 17, 2013 at 10:57 am
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;
October 17, 2013 at 12:01 pm
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 procedureto 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/
October 17, 2013 at 12:21 pm
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 procedureto 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?
October 17, 2013 at 12:35 pm
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.
October 17, 2013 at 12:39 pm
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/
October 17, 2013 at 1:46 pm
sqlblue (10/17/2013)
What is the best way for me to call thisstored 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.
-- Itzik Ben-Gan 2001
October 17, 2013 at 2:54 pm
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