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:



UPDATE [ Table

] SET Field1 = Value1,

Field2 = Value2, . . . WHERE PRIMARYKEY = TheKey




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:






CREATE TABLE [

dbo ] .

[ ChannelSubscribedByUser ] (

[ ID ]

[ int ]

IDENTITY ( 1 , 1 ) NOT

NULL , [

UserId ] [

int ] NOT

NULL , [

ChannelId ] [

int ] NOT

NULL , [

ReadRSSItemIDs ] [

image ] NOT

NULL , CONSTRAINT [ PK_ChannelSubscribedByUser ] PRIMARY KEY CLUSTERED ( [

UserId ] ASC

, [ ChannelId ] ASC

)




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.






ALTER PROCEDURE [ dbo

] . [

prcChannelSubscribedByUserUpdate

] @ID int , @ChannelId int , @ReadRSSItemIDs image , @UserId int AS UPDATE dbo. [

ChannelSubscribedByUser

] SET [

ChannelId ] =

@ChannelId , [

ReadRSSItemIDs ] =

@ReadRSSItemIDs , [

UserId ] =

@UserId WHERE [

ID ] =

@ID SELECT [ ID

] , [

UserId ] ,

[ ChannelId ] ,

[ ReadRSSItemIDs ] FROM

dbo. [ ChannelSubscribedByUser ] WHERE

[ ID ]

= @ID




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:






UPDATE Employee SET FirstName = @FirstName ,

LastName = @LastName ,

CompanyID = @CompanyID WHERE EmployeeID = @EmployeeID




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:






UPDATE dbo. [

ChannelSubscribedByUser

] SET [

ReadRSSItemIDs ] =

@ReadRSSItemIDs , WHERE

[ ID ]

= @ID




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.


Share on Tumblr
	


	
	







			

			20 thoughts on “Think you know how to write UPDATE statement? Think again.”		


		
		
    
					
  1. 
			
    
				

				
    
					
    I’m no good my love and have fun with I’m no good who else love extention green world Thanks you Buy zej.bravehost.com/ewa-sonnet.html super children Glad to see you time I’m no good Buy Order this come to you who else Good work! ewa sonnet 
    

    
				
    

				
    			
    
		
    2. 
		
  2. 
			
    
				

				
    
					
    Interesting…. 
    

    My Idea was to introduce more Foreign keys in a table so that I can enforce referantial intigrity…
    

    I have to think again……
    

    Thanx for sharing.
    

    
				
    

				
    			
    
		
    3. 
		
  3. 
			
		
    4. 
		
  4. 
			
		
    5. 
		
  5. 
			
		
    6. 
		
  6. 
			
		
    7. 
		
  7. 
			
		
    8. 
		
  8. 
			
		
    9. 
		
  9. 
			
		
    10. 
		
  10. 
			
    
				

				
    
					
    This is where decent a decent OR mapper like llblgen pro will be your best friend, it dynamically creates update statements based on the fields you actually change!
    

    
				
    

				
    			
    
		
    11. 
		
  11. 
			
    
				

				
    
					
    Omar, way to go… wish I could be ever like you. At least will try to make my son like you. Thats what I can say.
    

    
				
    

				
    			
    
		
    12. 
		
  12. 
			
		
    13. 
		
  13. 
			
		
    14. 
		
  14. 
			
    
				

				
    
					
    Hi,
    

     great article and i would like to say that i am a great fan of your, i have one eye on your blog all the time. regarding the update article i agree with your but can you shed some light how really do we handle a update statement when we are updating a form with 1-20 feilds. cause way i see it doing it with lot of if else will also have its over head. won’t it.  
    

    
				
    

				
    			
    
		
    15. 
		
  15. 
			
    
				

				
    
					
    Slms Omar,
    

    I must say I am impressed with your knowledge! From what I understand, you are advocating updates without references to foreign keys – i assume you would have a separate procedure to deal with such updates because they are called very infrequently and hence can take the hit? 
    

    or are you advocating no keys in the data layer and offloading that to the application layer like some extremely large databases?
    

    wa'slm,
    

    T
    

    
				
    

				
    			
    
		
    16. 
		
  16. 
			
    
				

				
    
					
    i am using 
    

    string sql = “UPDATE Payment_Getway SET paymentstatus = '” + status + “' WHERE CAST([ID] AS VARCHAR(20)) = '” + pgid + “'”;
    

    Execute_Query(sql);
    

    update statement is not executed.
    

    
				
    

				
    			
    
		
    17. 
		
  17. 
			
		
    18. 
		
  18. 
			
		
    19. 
		
  19. 
			
		
    20. 
		
  20. 
			
		
    21. 
		


		
	
	
		

		
Leave a Reply