Hello All,
I have a long stored procedure that does a lot of querying and generates a report. Since its a summary report, it calls a lot of other procs to get data. I am wondering if its possible to execute concurrent sql batches from with a proc in Sql server ...
many thanks
I think you'll have to do the concurrency in code (multithreaded sql calls) and concatenate the result at the end.
No, SQL Server does not do concurrency in the sense I think you mean.
How long does the code run for? Is is a problem?
Edit, based on comment.
11-20 seconds for a big summary report isn't bad at all.
If you submit the calls in parallel from the client, it may take the same or longer: if each query is fairly intense and is resource heavy, then you may max out the server by running them together and affect other processes. + then you have to assemble data in the final form in the client.
Your best bet is to report stuff like this from another database. Either based on transformations from your production database into an OLAP database (which will make the time delay go away), or at least a periodic (say, nighly) static snapshot (which will make the delay not matter - you can turn off locking because nothing will change).
Side benefit: Report readers will be a lot happier with reports run five minutes apart that give the same answers. Or 12 hours apart.
The benefit you will appreciate the most is that life will be simpler.