I recently met this problem and received disaccording answers from different information sources.
I have many users using an old client-server application to launch long-running procedures. They run for a minimum of 15 minutes to 60 minutes or more.
The procedures’ inner workings have already been examined from senior developers and from the DBA: no more optimizations are feasible.
Due to the high demand of processing and to the resource contention on the main database, I think that these activities should be queued.
The approach I’m suggesting is:
- group all data processing routines in one or more stored procedures
- modify the client application to “request” processing instead of directly executing it and receiving a job number
- manage a queue of SQL jobs launched from a Windows Service
- modify the client application to get the status of the user’s jobs
- organize jobs in “classes” with different characteristics (maximum run time for the job, start and stop hour of day in which execution is allowed,…)
What is your opinion about this solution?
I think this approach could solve this specific problem and represent the framework to be used for projects with similar needs.
I look forward to read your comments.