C# Execute Multiple SQL Statements

Question

I have a class that stores update statements. I cant execute these statements within my parallel for loop, as this is causing dead locks.

The statements are executed once the loop is completed, in a scenario for 100 rows it works okay. But certain scenarios generate over 100,000 statements. Executing these statements on a sequential loop takes too long.

What I wish to achieve is, upon adding 100 statements to the class, I execute these statements on a separate thread, and clear the statements variable, so that the next 100 can be added and executed.

I am new to multi threading. Is this achievable? If not what other option do i use to reduce the time for execution. Note that I can't change the statement logic as there are many other factors attached to it.

Let me clarify my scenario further. That table i have is more of a log table that keeps track of sql rows transferd to clients, so that i do not resend the same object twice. This is to reduce bandwidth usage as the objects are transferd over internet link.

Initialy i was executing each statement as soon as i got a reply from the client. This worked great when the loops were sequential, but proved to be too slow. So i opted to use parallel for loops. This is where all issues came up as the same table was being selected from, inserted and updated at virtually the same time causing dead lock.

So i decided to keep the statements in a list of strings to execute later.

I tried converting all strings into a single string using string.join but this gave me system out of memory exception. Thus executing them sequentially one by one. Now the transfer takes 5 minutes and the execution 30 minutes. So i am looking for a solution to this..

Answer 1

Since it sounds like you continuosly keep getting new stuff to insert into your database, you can use a SqlTransaction, on which you execute your statements whenever they are available. Then, once in a while you commit the transaction.

Check MSDN for an example on how to use it.

EDIT: If you on the other hand get a lot of statements to execute in one heap, use SqlBulkCopy if possible, like LoztInSpace says.

Answer 2

I've had great success with using SQLBulkCopy into a temp table then executing an UPDATE against that (actually a MERGE, but same principle).

We got a 5 minute batch down to a few seconds.