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.