Andreas Grabner About the Author

Andreas Grabner has been helping companies improve their application performance for 15+ years. He is a regular contributor within Web Performance and DevOps communities and a prolific speaker at user groups and conferences around the world. Reach him at @grabnerandi

Linq2Sql: Prevent performance issues when operating on multiple rows with Stored Procedures

Many of you bloggers out there have already covered the fact that Stored Procedures should be used for INSERTS, UPDATES and DELETES or in general when modifying multiple rows in a batch like statement. I also came across other blogs that discussed deleting or updated multiple entities with the standard interfaces of Linq2Sql – not highlighting the actual performance problem when marking entities with DeleteAllOnSubmit.

Whats going on when we delete multiple entities?

I wrote a test that clears two of my database tables from testuser accounts that will be created by a set of web tests. I want to run this test as a setup step before running all my other tests. In order to clear my two tables I came up with the following code:

// Query to return my test users
var usersToDelete = from user in db.Users where user.UserName.StartsWith("test") select user;
var userScoreToDelete = from score in db.UserScores where score.User.UserName.StartsWith("test") select score;
// Mark the entities for deletion
db.UserScores.DeleteAllOnSubmit(userScoreToDelete);
db.Users.DeleteAllOnSubmit(usersToDelete);
// submit the changes
db.SubmitChanges();

I have 5 testusers in my database – resulting in 5 rows in the table Users and 5 rows in the table UserScores. The following PurePath image shows the SQL that is executed by the code above:

SQL Statements by Linq2SQL when deleting multiple entities

SQL Statements by Linq2SQL when deleting multiple entities

Conclusion

In order to avoid performance problems with batch updates or deletes you should make use of stored procedures that can easily be called via Linq2Sql. Scott Guthrie has a great series of blogs about Linq2Sql. Check out his blog for more insight.

Trackbacks

  1. Linq2Sql: Prevent performance issues when operating on multiple rows with Stored Procedures…

    Thank you for submitting this cool story – Trackback from DotNetShoutout…

  2. [...] Reading: Blog on Linq2Sql Performance Issues on Database,  Video on Performance [...]

  3. [...] Reading: Blog on Linq2Sql Performance Issues on Database,  Video on Performance [...]

  4. [...] 延伸阅读:Blog on Linq2Sql Performance Issues on Database、Video on Performance Anti-Patterns [...]

Comments

*


two + = 3