Omar AL Zabir blog on ASP.NET Ajax and .NET 3.5

Think you know how to write UPDATE statement? Think again.

When I was a kid, my mom used to read me UPDATE queries every night before I went to sleep. I heared many stories about updating objects to database where most of them were the same old:

So, I grew up with the same ideas on how to update objects in tables as other kids do. All the UPDATE queries involved taking all the fields and the update stored procedures used to have all the properties of the objects. If you are using some Code Generators (e.g. Code Smith) and generating data access layer codes and stored procedures for objects, you will see almost all the code generators and ORM tools generate UPDATE statments with all the fields in the SET block. Let me show you with an example how evil this idea is.

Imagine a table like this:

This is a table from Pageflakes database. In this table, we store all the RSS feeds user has read from a particular RSS channel. UserId is a foreign key to User table and ChannelID is a foreign key to Channel table. Pretty straight forward. We had a harmless update stored procedure generated using Code Smith using the famous .NET Tiers template.

Look at the query plan and see how horrible it really is:

There are 2 Clustered Index Seeks, one Table Spool (very expensive), 2 Nested Loops, 1 Assert, 1 Clustered Index Seek. If you look at the IO Statistics, you can see how truly evil this query is:

Table 'RSSChannel'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PageFlakesUser'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ChannelSubscribedByUser'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 3, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

It is making SQL Server go to the tables which are refered as foreign key during a single row update!

Generally when we update a row in a table which represents an object, we rarely change the value of the fields which are foreign keys to other tables and has index on them. Most of the time, the updates are on the fields which contain properties, not relations. For example, 99% of the cases, you will update properties of an Employee object like FirstName, LastName, Age etc. 1% case you will modify the CompanyID (because s/he was fired) which is a foreign key to Company Table. But if you go to your database and see the stored procedure which updates the Employee object, you will see this:

Don't be ashamed. I know we all have queries like this every where.

If you remove those unwanted fields which generally have Foreign Key and Index on them, you can gain significant performance improvement. When I just change the UPDATE statement to this:

See the query plan:

There's just one Clustered Index Update. No Table Spool, no Clustered Index seeks, no nested loops, no asserts. The IO statistics shows how significant the improvement really is:

Table 'ChannelSubscribedByUser'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 3, lob physical reads 0, lob read-ahead reads 0.

There's just one update and nothing else. Also the number of Logical Reads is 6 compared to 15.

So, you thought you knew how to write UPDATE statements? Think again.