How much overhead does 'Update Check' have for LINQ UPDATES

Question

I have a simple row that I edit using LINQ. It has about 30 columns, including a primary key numeric sequence.

When an UPDATE is performed through LINQ, the UPDATE statement includes all the columns of the table (for concurrency checking).

I'm wondering how inefficient this is - if not negligibiel. Since there is an index on the primary key I assume that column is being used for the initial row search and then the other fields are being checked in addition. I wouldn't have thought this would take more than a negligible amount of time.

The reason I ask is that I've seen this UPDATE take over a second in some cases, which just doesnt seem right. There may be other long running operations things going on but it made me curious as to whether or not I should be worried.

I know I can set 'UpdateCheck' to never for all the other fields, but this is a pain.

Is there a way to turn off 'Update Check' for a single SubmitChanges(), or do I have to do it by changing 'UpdateCheck' for every field.

Any advice would be appreciated.

Here is the SQL update :

exec sp_executesql N'UPDATE [dbo].[SiteVisit]
SET [TotalTimeOnSite] = @p12, [ContentActivatedTime] = @p13
WHERE ([SiteVisitId] = @p0) AND ([SiteUserId] IS NULL) AND ([ClientGUID] = @p1) AND ([ServerGUID] IS NULL) AND ([UserGUID] = @p2) AND ([SiteId] = @p3) AND ([EntryURL] = @p4) AND ([CampaignId] = @p5) AND ([Date] = @p6) AND ([Cookie] IS NULL) AND ([UserAgent] = @p7) AND ([Platform] IS NULL) AND ([Referer] = @p8) AND ([KnownRefererId] = @p9) AND ([FlashVersion] IS NULL) AND ([SiteURL] IS NULL) AND ([Email] IS NULL) AND ([FlexSWZVersion] IS NULL) AND ([HostAddress] IS NULL) AND ([HostName] IS NULL) AND ([InitialStageSize] IS NULL) AND ([OrderId] IS NULL) AND ([ScreenResolution] IS NULL) AND ([TotalTimeOnSite] IS NULL) AND ([CumulativeVisitCount] = @p10) AND ([ContentActivatedTime] IS NULL) AND ([ContentCompleteTime] IS NULL) AND ([MasterVersion] = @p11) AND ([VisitedHome] IS NULL) AND ([VisitedStore] IS NULL) AND ([VisitedVideoDemos] IS NULL) AND ([VisitedProducts] IS NULL) AND ([VisitedAdvantages] IS NULL) AND ([VisitedGallery] IS NULL) AND ([VisitedTestimonials] IS NULL) AND ([VisitedEvolution] IS NULL) AND ([VisitedFAQ] IS NULL)',N'@p0 int,@p1 uniqueidentifier,@p2 uniqueidentifier,@p3 int,@p4 varchar(46),@p5 varchar(3),@p6 datetime,@p7 varchar(164),@p8 varchar(36),@p9 int,@p10 int,@p11 int,@p12 int,@p13 int',@p0=1009772,@p1='039A0614-31EE-4DD9-9E1A-8A0F947E1719',@p2='C83C0E68-142A-47CB-B7F9-BAF462E79429',@p3=1,@p4='http://www.example.com/default.aspx?c=183',@p5='183',@p6='2008-11-30 18:22:59:047',@p7='Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; SIMBAR={85B62341-3F6B-4645-A473-53A2D2BB66DC}; FunWebProducts; .NET CLR 1.1.4322; InfoPath.1; .NET CLR 2.0.50727)',@p8='http://apps.facebook.com/inthemafia/',@p9=1,@p10=1,@p11=30,@p12=6,@p13=6

Answer 1

We ran into this early on Stack Overflow. Every LINQ to SQL update verifies that the underlying fields haven't changed before writing an update. In other words, every update is "update the record only if this field equals, and this field equals, and this field equals"..

We decided most of the time we didn't care about pessimistic updates, and the only field that the update needs to check is the Id field.

So, what we did was set UpdateCheck="never" for every field except the Id in the dbml mapping file, like so:

<Type Name="Badge">
  <Column Name="Id" Type="System.Int32" DbType="Int NOT NULL IDENTITY"
      IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" />
  <Column Name="Class" Type="System.Byte" DbType="TinyInt NOT NULL"
      CanBeNull="false" UpdateCheck="Never" />
  <Column Name="Name" Type="System.String" DbType="VarChar(50) NOT NULL" 
      CanBeNull="false" UpdateCheck="Never" />

I don't know if there is a way to do this programmatically or on the fly.

Answer 2

Personally, I like the simplicity of a single timestamp/row-version column; set this as the only column to be checked (IIRC, happens automatically for timestamp), and you're sorted - you should then get TSQL like:

exec sp_executesql N'UPDATE [dbo].[SiteVisit]
SET [TotalTimeOnSite] = @p2, [ContentActivatedTime] = @p3
WHERE ([SiteVisitId] = @p0) AND ([Timestamp] = @p1)

This relies on their not being concurrent (non-conflicting) updates to the same record; with a timestamp/row-version etc, any conflicting update will cause the second to abort, even if they updated different columns etc.

Answer 3

Timestamp field certainly seemed to be the most elegant way of doing this. I HATE having to mess with individual field's properties - mainly so I can safely delete and re-add a table to my DBML file without having to worry about the consequences.

http://msdn.microsoft.com/en-us/library/bb470449.aspx

The SQL now generated for an UPDATE is :

exec sp_executesql N'UPDATE [dbo].[SiteVisit]
SET [TotalTimeOnSite] = @p2
WHERE ([SiteVisitId] = @p0) AND ([timestamp] = @p1)

and in the same transaction:

SELECT [t1].[timestamp]
FROM [dbo].[SiteVisit] AS [t1]
WHERE ((@@ROWCOUNT) > 0) AND ([t1].[SiteVisitId] = @p3)',N'@p0 int,@p1 timestamp,@p2 int,@p3 int',@p0=814109,@p1=0x0000000000269CB8,@p2=1199920,@p3=814109

It does an UPDATE, and then retrieves the new timestamp to send back to my client. I'm not sure i fully understand what @@ROWCOUNT > 0 means, but right now I dont really care :)

Answer 4

Your assertion that the overhead for the update check is negligible is correct. If there is an index (or primary key) that is satisfied by any part of the where clause, then that will be used. The cost for checking the other columns is negligible. You can confirm this by enabling the execution plan display in SQL management studio (or query analyzer for older versions of SQL Server) and run your update.

Long execution times is most likely caused by something else. Locking is a good candidate. If you can reproduce it, use SQL Profiler to find out what is going on.

Answer 5

If you can modify the schema add a column of type rowversion. The latest LINQ to SQL sets the update check to Never for all columns. If you have a timestamp, it will use that as an optimistic lock check, and the system bumps it every time there is an update.

NOTE: this used to be the Timestamp data type as defined by SQL '92, but the implemented it without any time information so it was not compatible with any other standard system. Maybe that was intentional, who knows.