Designing a Database Application with OOP

Question

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?

Answer 1

A "rule-of-thumb" to get blazing speed in a database is to use set-based commands instead of separate procedural style commands. It's not only the network round trips that affect performance. Even if you have the loop embedded in the sql, you'll find it orders of magnitude slower than a set-based command.

--SLOW way, procedural
loop i=0 to 999999
    update foobar set status='test' where id=i;
end loop

vs

--FAST way, set-based
update foobar set status='test' where id between 0 and 999999

Anywhere you see data processed in a loop, you can replace it with set-based commands. If there are changing conditions checked mid-loop then you may be forced to use procedural style. But that is very rare.

Just identify the bottle necks. You can keep the rest of the logic controlled with your objects. If you only operate on a single record, the procedural style is just as fast.

NOTE: internally within the RDBMS the "set-based" commands are executed via a procedural loop. Nothing wrong with loops per-say, it's just the interface provided by most RDBMS's are optimized to be used with set-based commands.

Answer 2

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

Link

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.

Answer 3

I would consider using one of the big-name ORM solutions like Entity Framework or NHibernate. The advantage of doing so is that they perform all kinds of neat tricks to optimise cals to the database. For example:

1. Caching, so you ony have to read values from the database once.
2. Tracking changes, so they only update fields that have actually changed.
3. Lazy loading, so they only read the records in a collection when you actually use them.

In addition, you won't be tied to a single table per class (the so-called "active record" pattern).

The down-sides to my suggestion are that your existing code base will need some (perhaps considerable) modification, and you have a steep learning curve ahead of you.

Nevertheless, having made a similar journey myself, I am pesuaded that this is a worthwhile direction of travel.

Answer 4

When you're processing a list of records, instead simply calling c.Save() you should pass them to some kind of aggregator that can analyze the records and write the appropriate SQL statements (or your classes can simply support an emitDML() method that returns the SQL to execute). Then, when you're done with the list, just tell the aggregator to persist any changes, and it can execute the SQL in a single transaction. If you're dealing with hundreds of records it might make sense to serialize them out to disk and invoke the bulk copy program to insert them, but that's a fair amount of work.