SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
sqlblue
sqlblue
Mr or Mrs. 500
Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)

Group: General Forum Members
Points: 522 Visits: 295
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;
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62795 Visits: 17959
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Ed Wagner
Ed Wagner
SSC-Forever
SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)

Group: General Forum Members
Points: 48787 Visits: 10844
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?


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
sqlblue
sqlblue
Mr or Mrs. 500
Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)

Group: General Forum Members
Points: 522 Visits: 295
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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62795 Visits: 17959
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Alan.B
Alan.B
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13438 Visits: 8001
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.

-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't 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. " -- Itzek Ben-Gan 2001
sqlblue
sqlblue
Mr or Mrs. 500
Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)

Group: General Forum Members
Points: 522 Visits: 295
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search