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

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.

Related posts:

1. Instrumenting SQL Server Managed Stored Procedures Starting with SQL Server 2005 a database developer has the...
2. Best Practices to Diagnose and Prevent AJAX Performance Issues in Complex Web2.0 Applications: Monster.com I am doing another Webinar on Best Practices for AJAX...
3. Top 3 Performance Problems in Custom Microsoft CRM Applications After spending a lot of time focusing on Client-Side Web...
4. Performance Analysis: How to identify synchronization issues under load? Synchronization is a necessary mechanism to control access to shared...
5. ASP.NET GridView Performance ASP.NET offers a powerful GridView control that can be used...