February 27, 2014 at 9:58 pm
I have a cursor of 5000 records that needs to have a SP run on each record. The SP does not have any code that clashes or can cause a deadlock. Each record is unique. The SP would work very well in a parallel setup.
Which is a better way to do the parallel processing.. Service Broker or SQL Agent (Job)? Why?
Thanks,
Mike
February 27, 2014 at 10:52 pm
I think we should go with SB because SQL jobs to schedule tasks is the resource consumption due to the inefficienct SQL cache utilization of SQL Server Agent and SB is there to resolve this issue. For large scale of batch processing we should prefer to use SB. It can take advantage of the queuing and parallel processing offered by SB to handle large volumns of data quickly and efficiently.
February 28, 2014 at 6:01 am
Agreed. if you are staying in the DB engine, I would use service broker. You can use threading if you have any code outside of the engine, but that does not sound like the situation here.
Mostly, build something that yourself and the next DBA can read and understand. Log everything as you go, which may require wrapping the existing procedure with another procedure.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply