How to call a stored procedure without waiting for it to finish?

Question

How do I call a stored procedure from a web service method without having to wait for the stored procedure to finish, and just let the method finish all other activities?

As an example (not actual situation but much simpler to understand): I have a table houses with 10 million rows, and each year I have to calculate what each house is worth based on soil, constructions, etc.

Before the year ends, I feed new parameters of soil and constructions, and then let the database calculate each house's worth (with a stored procedure called from a web service method), but it may take hours. In this same method, I want to be able to tell the system that I have started calculating (after it starts, not before).

So here How would I avoid a time out, and let my web app to continue doing other things.

thank you.

Answer 1

If you do not depend on the results of the stored procedure, you can wrap your stored procedure call inside a method and call it using

  Thread statisticsThread = new Thread(new ThreadStart(YourSPWrapper));
  statisticsThread.Priority = ThreadPriority.Lowest;
  statisticsThread.Start();

Answer 2

Are you talking about calling a SP which returns nothing? Like kicking off a job on the server?

You could try using an asyncronous delegate to do it, threadpool is probably the easiest:

ThreadPool.QueueUserWorkItem(myDelegateFunction);

protected void myDelegateFunction(object state) {
   //make your db call here and let the delegate fall out of scope
   //if you need to set a variable saying it succeeded, set a global here
}

You can then kick off the delegate whenever you want it to be done, and it will be executed in a asynchronous delegate when there are thread resources available.

Answer 3

Perhaps consider using SQL Server Agent to schedule the query as job? This is especially worthwhile if the job needs to run on a regular schedule or only very rarely.

If absolutely necessary, you can also use use a SQL script to schedule the job on demand from your webapp. Just ensure the agent service is running, otherwise your job won't start.

Answer 4

With IBM Informix Dynamic Server 11, you could consider running the procedure via the DB-Cron task scheduler. The program would initiate the operation as a synchronous database request, but the task would not need to complete before the request was complete.