Faster way to update 250k rows with SQL

Question

I need to update about 250 thousand rows in the table, and each field for updating will have a different value depending on the row itself (not calculated based on the row or key identifier, but externally).

I tried with a parameterized query, but it turned out to be slow (I can still try with the table-value parameter, SqlDbType.Structured, in SQL Server 2008, but I would like to have a general way to do this in several databases, including MySql, Oracle and Firebird).

Creating a huge number of separate updates is also slow (BUT is about 2 times faster than creating thousands of individual calls (roundtrips!) Using parameterized queries)

How to create a temporary table and run an update connecting my table and tmp? Will it work faster?

Answer 1

How slow is slow?

, ( , , ). , , "", - (, , , [, DB roundtrip ], , , ).

. , 1000 , . , , .

- "" - . , SQL Server " " "" " " , ( , - ).

. , , BULK INSERT UPDATE ( UPDATE 1000, ). , : ...

Answer 2

,

Answer 3

,

• .

, , .

Answer 4

SqlBulkCopy KeepIdentities.

SqlTransaction SELECT , , DELETE THEM, ( ) . # . # , , , SqlBulkCopy, , . . , .

Answer 5

:

• , , ///
• / .
• , , . + , .
• SQL , .

:

• , SQL, .
• , ,

:

• , ,

Answer 6

:

• • :
• • . codeka
• (RDBMS . , )
  • ,
• - RDBMS
  • , .
• • ,