DBML designer over SQLMetal


Today I got an assignment from client that the update query is running too slow. I started debugging it and found that the update is using all the fields. I came to know that LINQ to SQL by default assume that all columns participate in opt concurrency and generates a full WHERE clause.
So the query looks like,

UPDATE [dbo].[TableName]
SET [Column1] = @p1, [Column2] = @p2, [Column2] = @p3, [Column3] = @p4, [Column4] = @p5
WHERE [PRIMARYKEY] = @p0 AND
[Column2] = @p2 AND
[Column2] = @p3 AND
[Column3] = @p4 AND
[Column4] = @p5

From this link http://stackoverflow.com/questions/10136450/submitchanges-internally-adds-all-the-fields-as-where-clause-how-to-get-rid-of I came to know about a property UpdateCheck. This has to be set to NEVER in order to remove it from concurrency controlling. But this is not possible with SQLMetal tool because it generates the class structure straight away through command line.

Then I tried DBML designer. In DBML designer you can set the properties for every column from visual studio. So I dropped my table into the designer and it's respective code generated. Both the tools generate the same code, but designer facilitates you with properties window to change them at any point of time.
With UpdateCheck set to never the query gets generated like this,

UPDATE [dbo].[TableName]
SET [Column1] = @p1, [Column2] = @p2, [Column2] = @p3, [Column3] = @p4, [Column4] = @p5
WHERE [PRIMARYKEY] = @p0

To mention the disadvatages of setting UpdateCheck to never is the application looses concurrency control. The where clause gets cut down and allows multiple users to update the table at the same time.

No comments:

Post a Comment