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:
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.