Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to execute stored proc iin a loop for a date range Expand / Collapse
Author
Message
Posted Thursday, October 17, 2013 10:57 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 15, 2014 7:56 PM
Points: 74, Visits: 264
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;
Post #1505825
Posted Thursday, October 17, 2013 12:01 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:32 PM
Points: 13,302, Visits: 12,168
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 Moden's 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)
Post #1505848
Posted Thursday, October 17, 2013 12:21 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, August 28, 2014 1:00 PM
Points: 4,196, Visits: 3,235
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
Post #1505857
Posted Thursday, October 17, 2013 12:35 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 15, 2014 7:56 PM
Points: 74, Visits: 264
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.
Post #1505869
Posted Thursday, October 17, 2013 12:39 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:32 PM
Points: 13,302, Visits: 12,168
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 Moden's 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)
Post #1505870
Posted Thursday, October 17, 2013 1:46 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 7:30 PM
Points: 562, Visits: 2,618
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



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1505891
Posted Thursday, October 17, 2013 2:54 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, May 15, 2014 7:56 PM
Points: 74, Visits: 264
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
Post #1505915
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse