A quick note on ADO.NET 2.0 and SQL performance in a normalized database

Back again after a while...

So I recently came across the problem, that an ADO.NET typed DataTable could perform pretty bad when used with the default generated CRUD commands. The situation is that if you create a typed DataTable for an SQL table in VS2008 by using an SQL SELECT, then the dataset designer will create the appropriate INSERT, UPDATE and DELETE tableadapter commands for you (if you ask for it). The main drawback of this approach is its main advantage: these generated commands will be general enough to handle columns with default values, or values generated by database-side triggers on-the-fly, etc. And at the same time UPDATE will be fairly inefficient when these tables reside in a normalized model, as it will identically update all unmodified columns in the database also, mainly those which are the foreign keys of the table. Updating unmodified columns might causes totally useless but really expensive index updates, and slow foreign key checks (even if you're using smart indexing). These costs run higher quickly if the depth of the tables grow, and can easily turn to be the main bottleneck of the overall system performance.

In such cases I suggest to override default generated UPDATE command to only update those columns which are actually subject to change.