Benchmarking SubSonic's Slow Performance

Using the download from the SubSonic website and their ActiveRecord T4 templates I tested two things.

1. The default implementation of ActiveRecord.SingleOrDefault as generated by the template
2. The equivalent query as a CodingHorror query.

The results speak a little too loudly:

Running 500 itrations that load up a post entity
Mapper Query took 74ms
PetaPoco (Fast) took 75ms
hand coded took 77ms
Dynamic Massive ORM Query took 78ms
Dynamic Mapper Query took 81ms
PetaPoco (Normal) took 81ms
Linq 2 SQL Compiled took 129ms
SubSonic Coding Horror took 165ms
Linq 2 SQL ExecuteQuery took 274ms
Linq 2 SQL took 895ms
Entity framework ExecuteStoreQuery took 918ms
Entity framework took 1369ms
SubSonic ActiveRecord.SingleOrDefault took 5399ms

Ouch! I realize this test highlights a specific problem, but what's concerning is that this is the default usage pattern that I imagine many SubSonic users start out with.

Although most of this is in SubSonic itself, the template doesn't help. Look at the default implementation of SingleOrDefault:

public static Post SingleOrDefault(Expression<Func<Post, bool>> expression) {

    var repo = GetRepo();
    var results=repo.Find(expression);
    Post single=null;
    if(results.Count() > 0){
        single=results.ToList()[0];
        single.OnLoaded();
        single.SetIsLoaded(true);
        single.SetIsNew(false);
    }

    return single;
}      

There's two big problems with this.

Firstly it's inefficient because it hits the database twice. The Find method returns a IQueryable so the call to results.Count() hits the database with a SELECT COUNT(*) and the subsequent call to results.ToList() hits it again - this time retrieving all matching records (of which their could be thousands - though probably not).

Secondly it's arguably incorrect. SingleOrDefault is a fairly well known concept to .NET programmers and is supposed to throw an exception if there are more than one record in the set. SubSonic's version doesn't.

If you're stuck with SubSonic you might like to update your template to something like this:

    public static <#=tbl.ClassName#> SingleOrDefault(Expression<Func<<#=tbl.ClassName#>, bool>> expression) {
        var single=GetRepo().Find(expression).SingleOrDefault();
        if (single!=null)
        {
                        single.OnLoaded();
                        single.SetIsLoaded(true);
                        single.SetIsNew(false);
        }
        return single;
}

You'll get correct behaviour and some performance gain:

Running 500 itrations that load up a post entity
hand coded took 75ms
PetaPoco (Fast) took 77ms
Dynamic Massive ORM Query took 82ms
PetaPoco (Normal) took 84ms
Dynamic Mapper Query took 89ms
Mapper Query took 99ms
Linq 2 SQL Compiled took 128ms
SubSonic Coding Horror took 187ms
Linq 2 SQL ExecuteQuery took 283ms
Linq 2 SQL took 913ms
Entity framework ExecuteStoreQuery took 929ms
Entity framework took 1377ms
SubSonic ActiveRecord.SingleOrDefault took 4212ms

Obviously this is still pretty poor. If you're looking for something fast and lightweight you might like to consider PetaPoco, Massive or Dapper.

Here's what happened on one of our servers (which was not even under a lot of load) when we switched from SubSonic to PetaPoco:

OK, I think I've given SubSonic a hard enough time... sorry, I'll stop now.