CODEWITHSUNDEEP
.netsqlsql-server
dao
dao

Reputation: 39

Efficient SQL UPDATE command for many rows

My problem is not one of "Able to", but "able to quickly"

I have a table with a static number of rows for which multiple items per row need to be updated frequently. I'm doing this with .Net and SQL Server.

For testing, the table has around 5000 rows, 20 columns, and I'm updating 3 fields/columns per row every 5 seconds.

I've investigated some solutions but cannot get my update time to where I want (< 1 second):

  1. Performing 5000 SQL UPDATE commands: takes about 4 seconds
  2. Using a DataAdapter, Updating a DataTable, then performing a DataAdapter.Update: about 4 seconds (it just does multiple UPDATE commands like 1.)
  3. Using a large single UPDATE TABLE tablename col1 = CASE ... WHEN .... WHEN... : gives error "the query processor ran out of stack space...."

The goal is to take a bunch of data which is in memory and to persist it to a table so other applications or users can access this data. I assume there are effecient means to do this that I have not stumbled upon.

One restrictions is that I may not have access to the users SQL Server, so I'd like to do this programmatically in the .Net code using an UPDATE command or similar.

Upvotes: 0

Views: 1025

Answers (2)

Oleg Dok
Oleg Dok

Reputation: 21756

If your sql server version is 2008+ then I recommend to create stored procedure and pass into this SP table valued parameter with all the data to update. This will perform much more quickly

Upvotes: 2

RBarryYoung
RBarryYoung

Reputation: 56725

Have your code call the System.Data.SqlClient.SqlBulkCopy API to Bulk Insert your change data into a staging table, then use an UPDATE command to read the staging data and update your target table with it.

The reason for this approach is that all of the methods above suffer from the same basic performance bottleneck: getting the data into SQL Server in the first place. Once it's in there, there are many ways to turn that static data into an UPDATE, all with very good performance.

Upvotes: 5

Related Questions