October 20, 2009 at 4:05 pm
Note sure if this is better served in the SQL Jobs forum or not so apolagies if I selcted the wrong forum for this item.
For a few months now I've been manually updating several jobs that each contain multipl steps where each step is calling the same stored procedure but each step is passing a different set of criteira, namely dates. I'm tired of having to continually edit this thing because I have to change not only what dates are apssed but changing what data object to call the stored procedure for.
And so to that end I was thinking of doine the following in a single step so that when the Job has to be updated I need only change what values are assigned to the variables where as now I have to delete or add steps as well as edit every step. Note that some of teh beloow is psudeocode for simplicity and because of an NDA I can't post the actual source code.
<Variable Declerations here>
SELECT @iCounter = 1
SELECT @iCounterMax = 12
SELECT @dtStart = '2009/01/01'
SELECT @dtEnd = '2009/01/01'
WHILE @iCounter <= @iCounterMax
BEGIN
EXECUTE uspMy_Stored_Proc @Start = @dtStart, @End = @dtEnd
SELECT @dtStart = @dtStart + 1 /*1 month*/,
@dtEnd = @dtEnd + 1 /*1 month*/,
@iCounter = @iCounter + 1
END
The jest of the above is to call the Stored Procedure, passing a date range that currently is 1 month and having it called 12 times, once for each month within a year.
This seems to me to be a much easier to manage & use version then listing 12 individual steps where each is for a different date range.
Thoughts? COmments? Suggestions?
Kindest Regards,
Just say No to Facebook!October 20, 2009 at 5:07 pm
Assuming that the real code will use DATEADD instead of +1 to modify the dates, it seems like it would work better. What determines the values that you still need to manually modify each time? Are you talking about the start/end dates having to be modified constantly? Is there a reason for that? What determines what they are changed to?
October 20, 2009 at 5:16 pm
Garadin (10/20/2009)
Assuming that the real code will use DATEADD instead of +1 to modify the dates, it seems like it would work better. What determines the values that you still need to manually modify each time? Are you talking about the start/end dates having to be modified constantly? Is there a reason for that? What determines what they are changed to?
You are correct I am using the DateAdd to move forward in monthly incrmenets. The date range specified (a combination of the Date initially assigned to the Start date plus the number of months the Loop condition is set to ) does vary as does another argument that I left out which tells the SP what accounting entity to perform this action on; there are thousands of these in the DB.
Basically we've setup a Job to directly call the Native SP (what came with teh accounting software package we purchased) so that we can schedule rebuilding of financial data for lare date ranges after hours. The product itself has no built in way to do this.
Thanks for chimming in.
Kindest Regards,
Just say No to Facebook!October 20, 2009 at 5:23 pm
Depending on how many different entities / how long each takes / what kind of downtime you have in the evenings / etc, it might be worth looking into making the job a bit smarter.
Instead of you determining a problem and manually sending in entityID's, convert the outer loop to a cursor that pulls in a chunk of entities and runs the SP for each one of them, for each month. Depending on the amount of time it takes / how often it needs doing etc. etc, you could have it grab a different series depending on the day of the week, the day of the month, or any other criteria you come up with and run through that chunk.
I do something similar to this with rebuilding indexes and statistics with log tables to track history.
October 20, 2009 at 6:44 pm
We have stored procedures somewhat similar in nature that pull data from a number of similar databases into a single database. They are driven by a while loop through a table containing linked server and database names. Dates are passed as parameters. The loop builds and executes dynamic SQL strings based on those values.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 20, 2009 at 9:12 pm
I know that the use of cursors and loops for flow control is an acceptable use but... I just can't bring myself to write one if there is another way that's just as fast.
First, make it easy on yourself... build a control table that can be populated either manually for ad hoc runs or by some proc somewhere. Here's and example...
--===== This is the "job control" table...
-- It would only need to be created once and
-- would be truncated between runs...
CREATE TABLE dbo.JobControl
(
RowNum INT IDENTITY(1,1),
StartDate DATETIME,
EndDate DATETIME,
Entity INT
)
--===== Populate the job control with data.
-- You could probably do this with another query
-- or manually for small ad hoc runs.
INSERT INTO dbo.JobControl
(StartDate, EndDate, Entity)
SELECT 'Jan 2009','Jun 2009',104 UNION ALL
SELECT 'Jul 2008','Dec 2009',210
After that and with a little help from a Tally table, things get easy...
--===== Now, build the necessary queries from the data in the table...
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = (SELECT CHAR(10)
+ 'EXEC uspMy_Stored_Proc @Start = ''' + CONVERT(CHAR(8),DATEADD(mm,t.N-1,jc.StartDate),112)
+ ''', @End = ''' + CONVERT(CHAR(8),DATEADD(mm,t.N-1,jc.StartDate),112)
+ ''', @Entity = ' + CAST(jc.Entity AS VARCHAR(10))
FROM dbo.JobControl jc
CROSS JOIN dbo.Tally t
WHERE t.N BETWEEN 1 AND DATEDIFF(mm,StartDate,EndDate)+1
ORDER BY jc.Entity, jc.StartDate
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)')
--===== Print the command to see what it looks like.
-- This can be commented out for production... or not.
PRINT @SQL
--===== Uncomment this line for production.
-- EXEC (@SQL)
See the following article for more on what a Tally table is an how it works to replace loops...
http://www.sqlservercentral.com/articles/T-SQL/62867/
--Jeff Moden
Change is inevitable... Change for the better is not.
October 20, 2009 at 9:25 pm
I do that when building batch tests of stored procedures, Jeff, but a WHILE loop through a table of 20 or so rows is going to run fast enough that I haven't bothered to go back and rewrite the loops. I only learned about tally tables from you a couple of years ago. 😉
For what it's worth, I do populate the table with a rowID column which I can index on, so the while loop just has to increment it's row counter by 1 to find an exact match up to the maximum row. Once upon a time, I just looked for the top 1 row with a larger value than the current row (ordered by that value).
I live and learn... and I'm happy about both. 😀 😀
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 20, 2009 at 9:26 pm
I tend to agree with Jeff, but I don't see a huge problem with the cursor. The thing I don't like is putting this in a job step.
Instead I'd create a proc to handle the looping/execution of the other proc. Use a table of some sort to store the data you want to drive it, and then update the table with the parameters or values you need to use. You can even have your proc update the table back as it finishes execution of the acct proc. That way if something happens you know how much work is done, or where things failed.
October 20, 2009 at 9:32 pm
Steve, if I'm going to loop at all, I do a WHILE because I'm told it's bad to leave a cursor open if anything breaks. No danger of that with a WHILE loop. I say "I'm told" because I have rarely (never?) tried using a cursor in production. Things may have changed, but I'll probably still avoid them just because they are evil and must be destroyed.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 20, 2009 at 9:36 pm
Heh... like I said...
I know that the use of cursors and loops for flow control is an acceptable use...
... and I agree with all the reasons Bob stated for this 0.01% exception to the rule of avoiding cursors. And, in this case, the cursor or loop wouldn't be the slow thing.
As I also said... I just can't bring myself to actually writing one... 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
October 20, 2009 at 9:49 pm
I've used lots of cursors for small jobs like this, things where I need to run something as a one-off. I've never had issues, but they've mostly been small and even when they failed, I haven't seen memory leaks or issues.
The bigger reason for me is that what if the process breaks on item 3 in the while loop? I think it's slightly cleaner to build a loop more like Jeff is doing based on pulling data from a table. It's also more scalable to me. If I have 12 jobs like this, all picking up a row at a time from a driving table, I can spread them out to multiple clients/processes.
October 21, 2009 at 6:35 am
If something breaks in the middle of a loop, how is it different from breaking in the middle of one long sequential job with multiple steps? Either way, I (hopefully) have try/catch logic in place to log the error, and the values in place for that step.
I can spread them out to multiple clients/processes.
This is a very good point that I haven't had to address yet, because what we've been doing is "good enough." But I will definitely keep it in mind for the future.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 21, 2009 at 7:35 am
The difference is that when I did a lot of this, we didn't have try..catch logic.
It may or may not be different with try..catch. Haven't used it enough in breakage to see. If it's table driven, regardless of a cursor or not, then I suppose it's the same. You will rebuild the cursor or loop from unprocessed items.
October 21, 2009 at 7:41 am
Exactly. In the current loop, if the download fails for a particular db, we just log the error and move on to the next in the list. That could be done just as easily the way you and Jeff are talking about.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 21, 2009 at 9:58 am
Garadin (10/20/2009)
Depending on how many different entities / how long each takes / what kind of downtime you have in the evenings / etc, it might be worth looking into making the job a bit smarter.Instead of you determining a problem and manually sending in entityID's, convert the outer loop to a cursor that pulls in a chunk of entities and runs the SP for each one of them, for each month. Depending on the amount of time it takes / how often it needs doing etc. etc, you could have it grab a different series depending on the day of the week, the day of the month, or any other criteria you come up with and run through that chunk.
I do something similar to this with rebuilding indexes and statistics with log tables to track history.
These are some good points but unfortunately there is no fixed set of logic that I could realistically put in place that would properly determine whether or not this SP needs to run. Right now its needed when the Controller 9fancy word for head number cruncher) tells us that Financial totals are off and need to be "Rebuilt" for lack of a better term. I am however going to make it so all variables are dynamic and passed in via arguments. The only item tthat will then need to be manually edited each time is the scheduling of the job which can vary depending on what day of the week the controller reports a problem and if the IT guys have anything going on after hours that is not part of the normal schedule.
Thnks
Kindest Regards,
Just say No to Facebook!Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply