CODEWITHSUNDEEP
c#sqlmultithreading
Mustafa Güven
Mustafa Güven

Reputation: 15744

Calling the same stored procedure using different threads?

There is a stored procedure that does very heavy processes. It regenerates sql insert scripts while iterating over about 30 tables and after finished that process it starts to insert those scripts to a table named X. The process takes about 20 minutes and this is unacceptable. The last thing which you have to know is that the procedure calling by a webmethod which is created on .NET.

PS: Tables have indexes.

Here are my questions

  1. I want to use multi-threading to solve the problem. But not sure if it would help? I will slice up the sp into 5 pieces and call it from 5 different threads at the same time. I wonder that would help to decrease the meantime between the start and the end of the processing?

Upvotes: 0

Views: 2729

Answers (3)

Venkatramanan Lakshmanan
Venkatramanan Lakshmanan

Reputation: 63

I am also trying to call the same SP from the console application with multiple threads and i don't see that my SP is running parallel. Even though i call it multiple times using thread, it is executing 1 after the other. I am using the Sql server 2008, (express edition) and also i have configured the Parallel Processing in SQL Server 2008. but it is not running parallel. any ideas or suggestion will be greatly appreciated.

Thanks Venkat

Upvotes: 0

David Osborne
David Osborne

Reputation: 6791

Have you taken into account the performance hit you will get with a large amount of inserts into tables with indexes. If you haven't already done so, try the insert part of the process on tables with no indexes and measure any gains. If it proves to be beneficial, script the index creation and run it at after the inserts.

Threading may help but my experience is that optimizing the sql process is likely to give more benefit. I'll be interested to hear your findings.

Upvotes: 0

Kinexus
Kinexus

Reputation: 12904

Potentially, this would work as there will be no blocking or waiting for other sections of the stored procedure to complete. Obviously, you are still constrained by the physical resources of the server. To be honest, the only way you will tell for sure is to do it and measure the performance.

I would ensure that you analyse the dependencies of each part of the stored procedure accurately, then do it again just to make sure.

Good luck.

Upvotes: 2

Related Questions