c# - ExecuteSqlCommand parallel execution -

sql server 11.0.3, c# .net 4.5, wcf, ef v4.0.30319

i have following table store queries :

object_type     object_name         object_type_name    object_query 1               hub_person          hub                 merge ... 1               hub_gps             hub                 merge ... 2               lnk_person_gps      lnk                 merge ... 2               sat_curr_person     sat                 merge ... 2               sat_gps             sat                 merge ... 2               sat_hist_person     sat                 merge ... 

i have execute queries service in order specified object_type column. queries have same object_type can executed in order, why want parallelize execution of these queries if possible.

here function using, without parallelism :

public int executeloadsources()         {             int nb = 0;             using (pocdventities pocdb = new pocdventities())             {                 using (transactionscope transaction = new transactionscope(transactionscopeoption.required))                 {                     ienumerable<string> res = (                         in pocdb.loading_source_query                         orderby a.object_type                         select a.object_sqlsrv_query                     );                     foreach (string req in res)                     {                         pocdb.database.executesqlcommand(req);                     }                     nb = pocdb.savechanges();                     transaction.complete();                 }             }             return nb;         } 

i tried parallel.foreach :

public int executeloadsources()         {             int nb = 0;             using (pocdventities pocdb = new pocdventities())             {                 using (transactionscope transaction = new transactionscope(transactionscopeoption.required))                 {                     ienumerable<string> res = (                         in pocdb.loading_source_query                         select a.object_sqlsrv_query                     );                     int maxvalue = (from in pocdb.loading_source_query select a.object_type).max();                     (int = 1; <= maxvalue; i++)                     {                         ienumerable<string> res2 = (                             in pocdb.loading_source_query                             a.object_type ==                             select a.object_sqlsrv_query                         ).tolist();                         parallel.foreach(res2, req2 =>                         {                             pocdb.database.executesqlcommand(req2);                         });                     }                     nb = pocdb.savechanges();                     transaction.complete();                 }             }             return nb;         } 

the first time run function, the underlying provider failed on open., try again timeout expired. timeout period elapsed prior completion of operation or server not responding.

am doing right? suggestions?

i can kind of give abstract answer this, won't directly answer question (and don't have rep post comment instead) still valuable information type of problem.

there few issues trying do.

the first database contexts (and extension sql connections) , thread concurrency. absolutely cannot share connection across threads, must create new connection on each thread spawns in parallel. not bug deal because of connection pooling, performance hit minimal anyways. in case, need create context instance inside concurrency block.

the second performing parallel queries on database inside of transaction not strategy. reason when run multiple simultaneous queries on same database need mindful of isolation mode. depending on connection , global settings, hammering log/journal , invoking dtc. if entire set of queries must transactionally safe whole not benefit @ running queries in parallel , should instead run each query sequentially. if each individual query must transactionally safe, should create transaction context within concurrency block.

the last suggestion use task instead of parallel. having nested structure of tasks can run in parallel makes bit more organizational sense executing parallel concurrency blocks in loop. in case have master task (which wait() on) have child tasks each object_type. produce task structure iterating groupby query on query data set. said earlier though, suggestion , not necessary accomplish parallel execution of queries.

something else consider (similar task strategy), use async methods execute each query , simple function invoke each async method , await @ end. thought.