September 8, 2012 at 8:17 am
Hi Guys,
I have a store procedure, that does a Bulk Delete and Bulk Insert Operation of certain data related to one resource in a entity/table. Number of records that are deleted and inserted are around 10000 rows for a resource. The entity contains nearly 100000 rows. the entity is also used for report generation.
In order to avoid heavy load on table. The Store Proc that does the delete and insert operation is scheduled to be called for every 1 min for each resource. This way the process becomes slower but efficient.
I need to increase the process to be faster and efficient i.e. I need to know if i have to call the Store Proc simultaneously/concurrently for say 5 resource, what issues that can be faced and how it can be avoided.
Most of my senior DBA are of the opinion that it might result in deadlock.
Kindly clarify.
September 8, 2012 at 9:19 am
You won't get a deadlock since you are only accessing one resource. What will happen is that one execution might block the next one.
You can experiment with the size of the load and might find it loading faster. If it loads 1000 in less than a minute, try 50000.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply