Sign in to follow this  

[web] SQL procedure vs function vs job

Recommended Posts

The software I have does a calculation on an sql server that takes 5 hours each. I want to be able to
1.)kick of calculation(dataset1), calculation(dataset2)....etc from client pc
2.)shut my client pc down
3.)server continues to run the calculations while the client has disconnected.

What happens is if I run a query calling a function, the query waits 5 hours for a result and stays running. If I force close the application, I believe the connection dies and the function stops before the 5 hours is up.

If I run a procedure that calls the function, the query still waits for a result.

Am I supposed to kick off a job instead? I don't want a result, I just want to tell the server to do something and not have any strings attached to the client.

Share this post

Link to post
Share on other sites
anything can happen in 5 hours

you need to design the process so it works in chunks (say 1000 records at a time)
then you can create a job that calls a stored procedure every minute

if you need a higher frequency you can loop inside the stored procedure (but never inside a transaction)

loop 10 times {
begin tran
exec spblah
pause for 100 milliseconds
commit tran

this way it will be resilient to restarts etc.

Share this post

Link to post
Share on other sites
So the idea that a job running every 1 minute which if run will take 5 hours I could:

Upload to the server that I have a new computation (takes 5 hours).
When the job starts it checks if the last computation was complete, if not exit
Upload a 2nd computation.
A minute passes and the job is scheduled for a second time.

What happens here, will it cancel my first job, wait for the first job to finish before job 2, or will they run in parallel?

It will be too hard to break this computation up and start it up in sections. It is not sql but a server side DLL that is doing c++ work.

Share this post

Link to post
Share on other sites
For precisely this reason i built a job-queue system into my database. It accepts any number of "jobs" (database records with params) and will continue to churn on them until they are complete. If i upload a new job with the same params, it is smart enough to know this and does not commit a second job. It will not work on more than one job at a time.

This isn't standard DB functionality, though. You have to roll your own. The good news is that it is not very difficult.

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this