Minimise database updates from changes in DataTable/SqlDataAdapter

Question

My goal is to maximise performance. The basics of the scenario are:

• I read some data from SQL Server 2005 into a DataTable (1000 records x 10 columns)
• I do some processing in .NET of the data, all records have at least 1 field changed in the DataTable, but potentially all 10 fields could be changed
• I also add some new records in to the DataTable
• I do a SqlDataAdapter.Update(myDataTable.GetChanges()) to persist the updates (an inserts) back to the db using a InsertCommand and UpdateCommand I defined at the start
• Assume table being updated contains 10s of millions of records

This is fine. However, if a row has changed in the DataTable then ALL columns for that record are updated in the database even if only 1 out of 9 columns has actually changed value. This means unnecessary work, particularly if indexes are involved. I don't believe SQL Server optimises this scenario?

I think, if I was able to only update the columns that had actually changed for any given record, that I should see a noticeable performance improvement (esp. as cumulatively I will be dealing with millions of rows).

I found this article: http://netcode.ru/dotnet/?lang=&katID=30&skatID=253&artID=6635 But don't like the idea of doing multiple UPDATEs within the sproc. Short of creating individual UPDATE statements for each changed DataRow and then firing them in somehow in a batch, I'm looking for other people's experiences/suggestions.

(Please assume I can't use triggers)

Thanks in advance

Edit: Any way to get SqlDataAdapter to send UPDATE statements specific to each changed DataRow (only to update the actual changed columns in that row) rather than giving a general .UpdateCommand that updates all columns?

Answer 1

you might try is do create an XML of your changed dataset, pass it as a parameter ot a sproc and the do a single update by using sql nodes() function to translate the xml into a tabular form.

you should never try to update a clustered index. if you do it's time to rethink your db schema.

Answer 2

Isn't it possible to implement your own IDataAdapter where you implement this functionality ?

Offcourse, the DataAdapter only fires the correct SqlCommand, which is determined by the RowState of each DataRow. So, this means that you would have to generate the SQL command that has to be executed for each situation ...

But, I wonder if it is worth the effort. How much performance will you gain ?
I think that - if it is really necessary - I would disable all my indexes and constraints, do the update using the regular SqlDataAdapter, and afterwards enable the indexes and constraints.

Answer 3

I would VERY much suggest that you do this with a stored procedure. Lets say that you have 10 million records you have to update. And lets say that each record has 100 bytes (for 10 columns this could be too small, but lets be conservative). This amounts to cca 100 MB of data that must be transferred from database (network traffic), stored in memory and than returned to database in form of UPDATE or INSERT that are much more verbose for transfer to database.

I expect that SP would perform much better.

Than again you could divide you work into smaller SP (that are called from main SP) that would update just the necessary fields and that way gain additional performance.

Disabling indexes/constraints is also an option.

EDIT:
Another thing you must consider is potential number of different update statements. In case of 10 fields per row any field could stay the same or change. So if you construct your UPDATE statement to reflect this you could potentially get 10^2 = 1024 different UPDATE statements and any of those must be parsed by SQL Server, execution plan calculated and parsed statement stored in some area. There is a price to do this.