ExecuteNonQuery in parallel as part of a common OleDbConnection / OleDbTransaction

Question

Found that OleDBConnection is not like ThreadSafe. It looks like he is trying to open several connections.

//doesn't work
using (OleDbConnection oConn = TheDataAccessLayer.GetConnection())
using (OleDbTransaction oTran = oConn.BeginTransaction())
Parallel.ForEach(ORMObjects, (ORMObject, State) =>
{

        if (!State.ShouldExitCurrentIteration && !State.IsExceptional)
        {
              var Error = ORMObject.SomethingThatExecutesANonQuery(oConn,oTran)

              if (Error.Number != 0)
                  State.Stop();

        }

});

If I block the connection for ExecuteNonQuery, the errors go away, but the performance tanks.

 //works
    using (OleDbConnection oConn =  TheDataAccessLayer.GetConnection())
    using (OleDbTransaction oTran = oConn.BeginTransaction())
    Parallel.ForEach(ORMObjects, (ORMObject, State) =>
    {

            if (!State.ShouldExitCurrentIteration && !State.IsExceptional)
            {
              lock(oConn)
              {
                    var Error = ORMObject.SomethingThatExecutesANonQuery(oConn,oTran)

                if (Error.Number != 0)
                      State.Stop();
             }

            }

    });

Let's pretend that

• I can’t change the nature of ORM: SQL cannot be bulk

• Business rules require interaction to be performed in a single transaction.

So:

• Is there a better / more efficient way to parallelize OleDb interactions?

• If not, is there an alternative to the OleDb client that can take full advantage of parallelism? (Maybe a native MSSQL client?)

Answer 1

ACID, "" . , IO SQL , .

, SQL- , . :

• SQL [A], . , , (.. [A] ).
• SQL [B], . , [B] , . / [A] .
• , - .
• .
  • , .
  • , , . BTW, "", , MS SQL Server (AFAIK).

, , "- parallelism" , , WHERE, - , , .

, , . !

---

BTW, MARS - MSDN: " , , MARS , ."

Answer 2

, OleDBConnection ThreadSafe.

, :

(Shared in Visual Basic) . .

, OLE DB . , , OleDbConnection ADO.NET , - , ADO.NET.

Answer 3

, Parallel.ForEach foreach . , .

Answer 4

, Parallel.ForEach. , .

, , min max .

Try this approach and use the stopwatch class to increase performance between different approaches and choose the one that works best in your case. It depends on the type of queries that you will perform against the database and schema.