Database – Designing a Database Application with OOP

databasenetoptimizationsql

I often develop SQL database applications using Linq, and my methodology is to build model classes to represent each table, and each table that needs inserting or updating gets a Save() method (which either does an InsertOnSubmit() or SubmitChanges(), depending on the state of the object). Often, when I need to represent a collection of records, I'll create a class that inherits from a List-like object of the atomic class.

ex.

public class CustomerCollection : CoreCollection<Customer>
{

}

Recently, I was working on an application where end-users were experiencing slowness, where each of the objects needed to be saved to the database if they met a certain criteria. My Save() method was slow, presumably because I was making all kinds of round-trips to the server, and calling DataContext.SubmitChanges() after each atomic save.

So, the code might have looked something like this 

foreach(Customer c in customerCollection)
{
   if(c.ShouldSave())
   {
       c.Save();
   }
}

I worked through multiple strategies to optimize, but ultimately settled on passing a big string of data to a SQL stored procedure, where the string has all the data that represents the records I was working with – it might look something like this:

CustomerID:34567;CurrentAddress:23 3rd St;CustomerID:23456;CurrentAddress:123 4th St

So, SQL server parses the string, performs the logic to determine appropriateness of save, and then Inserts, Updates, or Ignores.

With C#/Linq doing this work, it saved 5-10 records / s. When SQL does it, I get >100 records / s, so there is no denying the Stored Proc is more efficient; however, I hate the solution because it doesn't seem nearly as clean or safe.

My real concern is that I don't have any better solutions that hold a candle to the performance of the stored proc solution. Am I doing something obviously wrong in how I'm thinking about designing database applications? Are there better ways of designing database applications?

Best Answer

In modern SQL Server versions (> 2008 IIRC) you can use User defined types. So your sproc can actually take an array of user types:

http://blogs.msdn.com/b/felixmar/archive/2010/10/27/how-to-create-and-execute-a-stored-procedure-using-a-table-as-a-parameter.aspx

In our app we actually use the repository pattern, but hack it a bit to get around slowness issues like you describe.

So, in your example, I'd do something like this:

using (CustomerBulkContext ctx = customerCollection.GetSaveContext())
{

foreach(Customer c in customerCollection)
{
   if(c.ShouldSave())
   {
       c.Save(ctx);
   }
}
ctx.Save();
}

The idea is that all the customers are added to an in-memory list inside the context, and then the actual commit to the DB is handled via the save context's .Save() method. That method then creates a list of SQL objects and hands them to a sproc that does the bulk update/insert/delete.

Related Topic