Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
1  
nothing is going to beat SQL for CRUD –  Ryathal Oct 17 '12 at 18:23
    
I think I can accept that as a very reasonable statement. I was just dumbfounded at how enormously more efficient the stored procedure was. –  Tim C Oct 17 '12 at 18:32
    
Check if you have connection pooling enabled. Each pass of the loop may be literally opening and closing a connection, skewing the performance comparisons. –  mike30 Oct 18 '12 at 13:50
    
BTW although your stored proc solution is faster, it is extremely slow itself. 100 records per second is crawling like a snail. –  mike30 Oct 18 '12 at 13:52
    
Mike - in this system, the user does not manage more than 150 records at a time, so sub-second is acceptable. I've never tested the system beyond those specifications, because the business rule explicitly prevents a user from having more records than that. Now that I say that, I realize I probably should test it anyway... –  Tim C Oct 18 '12 at 20:55
add comment

4 Answers

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.

share|improve this answer
1  
We do the same thing as Timothy to allow for massive db throughput –  scarpacci Oct 17 '12 at 20:00
    
I keep stumbling into the repository pattern, but have been slow to change. It might be just what was in order. @scarpacci - I upvoted your comment for the "massive db throughput" part –  Tim C Oct 17 '12 at 20:25
    
scarpacci is right. There's no reason you cant hit > 10,000 record updates a second with a method like this. –  Timothy Baldridge Oct 17 '12 at 21:33
add comment

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.

share|improve this answer
    
A simple update statement wasn't really in order because most of the Saves would have been inserts. There was also a tremendous amount of logic that didn't really lend itself cleanly to an update statement. I understand your point though, and thank you for that input. An update might have covered at least part of the requirements. –  Tim C Oct 17 '12 at 20:22
    
It applies to inserts too. A very big data set will typically exist on disk beforehand. You bulk load into a staging table. Then use a set based insert(s) to move data to the target table(s). The "shouldSave" logic would be part of the where-filter on insertion to target tables. –  mike30 Oct 18 '12 at 17:41
add comment

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.

share|improve this answer
    
Argh. It was tough enough for me to move to link...but your point is definitely taken. I do find ORMs intriguing at an abstract level. –  Tim C Oct 17 '12 at 20:24
    
We use EF for our project, and I really hate it. It's bloated, slow and horribly complex. –  Timothy Baldridge Oct 17 '12 at 21:32
    
@TimothyBaldridge: Which version? Which approach (code/model first)? –  Kramii Oct 18 '12 at 5:49
    
I've used EF 2 and EF 4. In our system we have around 200 table/views each with about 3+ FKs to other entities. On application startup, EF takes about 20sec to get up and running (first query hit). Once it is up and running we rarely use half it's features. In a 2 tier app, with a repository layer, the EF objects never get cached (could cause issues with multiple servers), and they never get out of the repositories (we map them to DTOs). Because of all that, EF basically gives us insanely slow startup, tons of overhead, and a boatload of features we don't want or need. –  Timothy Baldridge Oct 18 '12 at 12:11
    
oh, this is model first. Which has even more problems as the only way to configure some things is by finding a table in the GUI (have fun hunting for the table you're missing), or by dropping down into the massive edmx file. Oh...and the output size of the EF dll (edmx compiled dll) is around 2MB. Really? 2MB just so I can do some joins with LINQ? –  Timothy Baldridge Oct 18 '12 at 12:14
show 1 more comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.