Parallelizing massive attachments in SQL Server with C # (for better performance)

Question

Problem: how to parallelize inserts in SQL Server (2008)

I do bulk numerical computation for scientific research in C # of multithreaded workers who basically do one thing: test thousands of possible configurations (matrix combinations) over a period of time (in days) and save the results in a SQL Server database.

If I store the results one after the other in the database (~ 300,000 rows per session * 100 sessions), one after another, I end up waiting hours for the storage process to complete.

Database design is very simple:

• Combined sets
  CS_ID1, value A1, value B1, value C1
  CS_ID2, value A2, value B2, value C2
  .........
  
  
• Results for the day
  CS_ID1, Day1, Result 1
  CS_ID1, Day2, Result 2
  CS_ID1, Day3, Result 3
  .........
  
  .........
  CS_ID2, Day1, Result N
  CS_ID2, Day2, Result N + 1
  CS_ID2, Day3, Result N + 2
  

" " , #, LINQ/SQL . , . : , ,

: , ( ). , , , , , - (, "" -:-))

, .

, , # SQL Server DBA ( , , , , :-))

12 24Go RAM

---

EDIT: Tiebreaker
: # SQl ( , , ). NLog, , , . SQL: , . , .

Answer 1

, , . SQL-, SQL , -

BEGIN TRANSACTION;

...<various SQL statements>...

COMMIT TRANSACTION;

SQL- .:

http://msdn.microsoft.com/en-us/library/ms188929.aspx

http://msdn.microsoft.com/en-us/library/ms190295.aspx

, , , , .

EDIT:

(?) , . , , - , . , , .

, -, , , , , . , , , >= 12, .

(, , ), , , , DB .

, , , , , , . , , .

, . , , , , - , .

Answer 2

300 000 - , , . . ETL SSIS 2008 2,36 /, 300 . .

:

• . . , , , . , (LDF) , . . , , :

:

do
  {
  using (TransactionScope scope = new TransactionScope(
     Required, new TransactionOptions() {IsolationLevel = ReadCommitted))
  {
    for (batchsize)
    {
      ExecuteNonQuery ("Insert ...")
    }
    scope.Complete ();
  }
} while (!finished);

• , SqlBulkCopy

3000 (~ 2 300 .). . , :

• b- ( )
• NUMA , . , .

: .

Answer 3

BULK INSERT .

Answer 4

, #: http://blogs.msdn.com/b/nikhilsi/archive/2008/06/11/bulk-insert-into-sql-from-c-app.aspx

# : #?

, .

Answer 5

,

, SQL .

, .

Answer 6

Parallel For ...

... BULK INSERT Batch commit ...

Answer 7

. -, ? "" ? / 24 ?
- " " " ", , "" . , , "" , , . , , "" , , ( , ).