Best way to update SQL table with reference to Datatable C #

Question

I have a data table containing an Id column. The identity column corresponds to the ID column of my table. I need to use these identifiers to set a single value in my sql table. But the structure of the data table does not match my table structure, i.e.

Data Table Sql Table ____________ _______________________________________________ ID ID | col 1 | col2 | col3 | col4 |final value 1 1 | a | b |c |d |N 2 2 | x | y |z |a |N 

A data table at any given time will contain more than 500,000 records.

My question is:

1) whether it will be faster to create an update expression and execute them in a parallel loop within a single transaction, as shown below:

 Sqltrn = Sqlconn.BeginTransaction(); Parallel.For (0; Datatable.Rows.Count; i => { SqlCmd.CommandText = BuildStatemet; SqlCmd.ExecuteNonQuery(); }); SqlTrn.Commit(); 

And will the above method lock the lock on my table?

or

2) Execute one statement, creating a related row from the dt tables as such:

 Update MyTable set FinalValue = 'Y' WHERE ID in (CreateConcactedStringHere) 

Which method is faster and safer since I need to complete the update as soon as possible, and also avoid locking the table due to its main transaction table in my database. Or is there any other way I can achieve this?

Answer 1

Updating individual rows will be slower than one update from the selected one. for this I would advise

• in your database, create a table similar to the following TicketTable {TicketID, TargetID, SomePrecalculatedRes1, ..., SomePrecalculatedResN}
• In C #, a DataTable is created with the same structure as the table from above
• Fill the contents of the table with the data that you will use for your update. Each row has a DataTable to have the same ticketId. (Parallel processing can give you several advantages)
• Bulk pasting DataTable content into db.TicketTable (see SqlBulkCopy )
• create an update team that takes into account your ticket and updates your target table from your selected ticket table.