Coping with long running stored procedures

G

guest2013-1

guest
Joined
Aug 22, 2003
Messages
19,800
Reaction score
13
We have a massive data update that should run nightly. Obviously this *can* increase (the data)

Now my client's senior programmer want it to run through a web service. However I'm not too sure this is the best method because we obviously have had timeouts in cases like this. They're also insisting on a manual override button (which can run through the website and get kicked off like that) but anyone here would agree that you're not assured that the stored procedure will continue running to the end if something times out or the web page gets closed.

So obviously I was thinking of running this on a SQL Job, however, the 1 (there's 3) stored procedures currently run hot at about 4 hours (for the 1, still timing the others)

Next is to run SQL Profiler but I'm pretty sure I've done what I can speed wise. How would you guys handle the request from my client?

a) Needs to run every 24 hours
b) Needs to have a manual override in case it didn't run (**** knows how they're going to be able to tell with a SQL job?)
c) Needs to work reliably...

any suggestions? I'm out of most...
 
I would also recommend a SQL Job too. With option b, there should be a way to see if your query is running currently ... (your sql job kicks of a stored proc and you monitor if the stored proc is running...).

We have sort of similiar setup. Not long running sql.. but update the lookup tables. Runs every evening, but if they update during the day, I just run the stored proc manually.
 
A log table is essential, with all writing to the log table being done using no locks option.
Then another process must watch and be able to report against the log table.
Then, the web service returns the current state of the log-table if it is running instead of starting it again.
Now, when the stored procedure is started, it will get a process ID (from sql server (sysprocesses)), and that ID will be valid for the duration of the stored-proc session. This ID must be stored in the log table as well, so that the current state web service can see if the stored procedure is still running. (Or if it perhaps disappeared in a server reboot).

Fun stuff.
 
Truly funstuff indeed. makes my head hurt

*heineken*

Okay now it hurts less
 
Yea SQL Job with some event logging as part of your stored proc.

I.e. , insert into a table that the job step started , and update the entry to "success" or something when it's done. If the job is interrupted/do not finish/times out you can then easily see this in your logging table.

SQL Jobs themselves -also- logs their results, but not necessarily in the detail you might want [i.e. you might want to record how many rows of data was transferred up to the point of failure etc].

As for the web service timeout thing, i would look into asynchronous calls if i were you. No reason to keep the web service itself tied up, kick off the job/SP on the server on it's own thread and poll the results at intervals if need be?

One project i basically did the following:

1.SQL Stored Proc doing a bunch of work [needs to happen at night]
-This SP logged all kinds of things about it's execution status into another table
-It checked previous results to determine how it should execute next.

2.Create a SQL Job that executes the SP at 2am in the morning [or whatever schedule]

3. Build into my application [web and windows app] ability to execute the job on demand [this would just trigger it and will thus run on the server, client side all resources are released] and then the app can poll the logging table for status updates if need be.
 
Are you *sure* you've done all you can to improve it, speed-wise? I've found that there's always more optimization to be done on a SP. A huge impact is the fields you select from joins though - if you select the incorrect field (i.e. a PK vs. FK) in the SELECT and later on filter on those results, the data might not be filtered correctly and you end up touching each record instead of only the filtered results.
 
AcidRaZor said:
We have a massive data update that should run nightly. Obviously this *can* increase (the data)

Now my client's senior programmer want it to run through a web service. However I'm not too sure this is the best method because we obviously have had timeouts in cases like this. They're also insisting on a manual override button (which can run through the website and get kicked off like that) but anyone here would agree that you're not assured that the stored procedure will continue running to the end if something times out or the web page gets closed.

So obviously I was thinking of running this on a SQL Job, however, the 1 (there's 3) stored procedures currently run hot at about 4 hours (for the 1, still timing the others)

Next is to run SQL Profiler but I'm pretty sure I've done what I can speed wise. How would you guys handle the request from my client?

a) Needs to run every 24 hours
b) Needs to have a manual override in case it didn't run (**** knows how they're going to be able to tell with a SQL job?)
c) Needs to work reliably...

any suggestions? I'm out of most...
Click to expand...

Your clients "senior" developer is a fking moron :)
 
FarligOpptreden said:
^ goes hand-in-hand with my suggestion... But somehow I think he would've already done that. That's usually the first step in query optimization.
Click to expand...

Its usually the first step and the most skipped step.

At my office we deal with tables on average of about 20-40million records,you feel the pain if queries are not written properly.
 
You say running for 4 hours? That must be on a few million records then. We ave SPs running on 5mil+ records and running only for about 30mins. If you have indexes check it and make sure its correct, simply indexing all the columns can cause bad performance.
 
maybe the organisation needs to ask the difficult question : "Why is it neccessary to do such a massive update, and isnt it better to make some changes ?"

Sounds like if you have a single problem, then you are buggered. 4 hours is way too long for a single job.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X