0

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.

dao
  • 39
  • 3
  • Stored Procedure is that solution which you really need. Have you tried batch update? Something like this: UPDATE TABLE tableName SET (column1 = table2.column1 FROM table2 WHERE ...). Check indexes on table it might make you database work slower. – Disposer Jan 24 '12 at 02:54
  • I've also used an indexed column for the WHERE portion of the UPDATE clause to make the row lookup efficient. – dao Jan 24 '12 at 02:59
  • Update column values are not in another table. If I go the Stored procedure route, can I create the Stored Procedure from the .Net code (no access to SQL Server)? – dao Jan 24 '12 at 03:01
  • @dao You create Stored Procedure first using Management Studio. You can than call it from .NET using: Command.CommandType = CommandType.StoredProcedure. – RKh Jan 24 '12 at 04:23
  • The one problem is I may not have access to SQL Server or Management Studio. BulkCopy function fixed my performance problem. – dao Jan 25 '12 at 19:06

2 Answers2

5

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.

RBarryYoung
  • 55,398
  • 14
  • 96
  • 137
  • Yet this will also require deleting the rows from the staging table each time before the next BulkCopy, otherwise, this table will grow quickly. Is this frowned upon as a database operation? – dao Jan 24 '12 at 03:22
  • Yes, that's correct you should delete them. And no, this shouldn't be a problem. – RBarryYoung Jan 24 '12 at 03:25
  • Best performing option available. Also, bulk uploading into staging tables and then updating tables would be a great application of the new Merge statement (2008) – J Cooper Jan 24 '12 at 03:40
  • 1
    Update...After implementing BulkCopy... I reduced update time by 80% using this method to test 5000 record updates vs 5000 UPDATE commands. Tip: If using a DataTable for the BulkCopy argument, when modifying the DataTable rows calling DataRow.BeginEdit for each row, then modify rows, then call DataRow.EndEdit for each row will significantly improve time to update the DataTable if you do not need the row events. Barry - thanks for the tip. – dao Jan 25 '12 at 18:58
2

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

Oleg Dok
  • 21,109
  • 4
  • 45
  • 54
  • 1
    Yep, this is the other good answer. I've never had a chance to try it, but I understand that it performs very well. – RBarryYoung Jan 30 '12 at 18:22