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

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

Solution

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.