linq: SQL performance on high loaded web applications

Question

I started working with linq to SQL several weeks ago. I got really tired of working with SQL server directly through the SQL queries (sqldatareader, sqlcommand and all this good stuff). 

After hearing about linq to SQL and mvc I quickly moved all my projects to these technologies. I expected linq to SQL work slower but it suprisongly turned out to be pretty fast, primarily because I always forgot to close my connections when using datareaders. Now I don't have to worry about it.

But there's one problem that really bothers me. There's one page that's requested thousands of times a day. The system gets data in the beginning, works with it and updates it. Primarily the updates are ++ @ -- (increase and decrease values). I used to do it like this

UPDATE table SET value=value+1 WHERE ID=@I'd

It worked with no problems obviously. But with linq to SQL the data is taken in the beginning, moved to the class, changed and then saved.

Stats.registeredusers++; Db.submitchanges();

Let's say there were 100 000 users. Linq will say "let it be 100 001" instead of "let it be increased by 1".

But if there value of users has already been increased (that happens in my site all the time) then linq will be like oops, this value is already 100 001. Whatever I'll throw an exception"

You can change this behavior so that it won't throw an exception but it still will not set the value to 100 002.

Like I said, it happened with me all the time. The stas value was increased twice a second on average. I simply had to rewrite this chunk of code with classic ado net.

So my question is how can you solve the problem with linq

Answer 1

Linq to Sql supports "optimistic" concurrency out of the box. If you need tighter control, you can add a Timestamp column to your table, and Linq to Sql will use that timestamp to tighten the concurrency.

http://mtaulty.com/CommunityServer/blogs/mike_taultys_blog/archive/2008/07/01/10557.aspx

However, as Morten points out in the comments below, this solution is not going to perform well. Of course, you can always use ADO.NET to update the value, just like you were doing before; that won't adversely affect the operation of your Linq queries at all.

Answer 2

You could turn off concurrency on that property by changing the UpdateCheck value:

http://msdn.microsoft.com/en-us/library/bb399394(v=VS.90).aspx

Messy if your using generated code and the designer but I think this is the only way to do this.

Answer 3

For these types of "write-only queries" I usually use a Stored Procedure. You can drag the stored procedure into the designer and execute it through the Linq to SQL DataContext class (it will be added as a method).

Sorry for the trite answer but it really is that simple; no need to to finagle with raw ADO.NET SqlCommand objects and the like, just import the SP and you're done. Or, if you want to go really ad-hoc, use the ExecuteCommand method, as in:

context.ExecuteCommand("UPDATE table SET value = value + 1 WHERE ID = {0}", id);

(But don't overuse this, it can get difficult to maintain since the logic is no longer contained in your DataContext instance. And before anybody jumps on this claiming it to be a SQL injection vulnerability, please note that ExecuteCommand/ExecuteQuery are smart methods that turn this into a parameterized statement/query.)