Stored procedure processing

Question

I am developing a console application. After performing the initial checks and preliminary details, I should enter 5 flows based on the rows of the sql table. let's say, for example, if my table consists of 1000 rows, then I will have to split the rows and assign them equally to all 5 threads, where in this case I will split and assign (create a datatable) 200 rows to each thread, and then all 5 threads will call SP simultaneously with the data. Is there any thing that will be processed in SP, since it will be executed in parallel by 5 threads and how can I handle insert, delete and truncation inside the SP? will this be a problem if you insert, delete, or trim multiple queries that run simultaneously, does any collision occur? or are there any locking functions in SP to allow only one operation for sql statement in SP?

Since I am new to this, I need help figuring out the problems that are occurring, perhaps, and ways to overcome it. it would be great if I got any tips and tricks in this scenario.

Thanks in advance.

abivenkat

Answer 1

Look at the INSULATION LEVEL option in SQL Server. It should be set as high as possible to prevent data corruption, however, which can and will lead to a large number of locks, and parallel operation can make the situation worse, not better.

Depending on the code of your procedure, you may even get deadlocks that will simply roll back from some calls that need to be reissued, which can cause additional deadlocks, and before you know it, your database is in completely crap 'd.

What I would recommend for such intensive data operations is not to paralyze them at all, just run them one by one. If your server is a multi-core machine, it can even paralyze the situation on its own.

Of course, these are wide hand waves. To get specific information, it is necessary to analyze the data structure (along with any indexes), as well as the code that will be executed.