sql server - 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.