Subsonic linq using activerecord very slow compared to simplerepository

Question

Anyone know anything about why linq queries are about 6 times slower when querying using active record vs simplerepository? The below code runs 6 times slower than when i query the data using a simple repository. This code is executed 1000 times in a loop

Thanks in advance

        string ret = "";            
//      if (plan == null)
        {
           plan =VOUCHER_PLAN.SingleOrDefault(x => x.TENDER_TYPE == tenderType);
        }
        if (plan == null)
           throw new InvalidOperationException("voucher type does not exist." + tenderType);

        seq = plan.VOUCHER_SEQUENCES.First();
        int i = seq.CURRENT_NUMBER;
        seq.CURRENT_NUMBER += seq.STEP;
        seq.Save();

Answer 1

We did some profiling on this and found SubSonic's record.SingleOrDefault(x=>x.id=someval) to be up to 20x slower than the same query done through CodingHorror. Logged it here: https://github.com/subsonic/SubSonic-3.0/issues/258.

The profiler pointed at this in ExecutionBuilder.cs:

// this sucks, but since we don't track true SQL types through the query, and ADO throws exception if you
// call the wrong accessor, the best we can do is call GetValue and Convert.ChangeType
Expression value = Expression.Convert(
    Expression.Call(typeof (Convert), "ChangeType", null,
                    Expression.Call(reader, "GetValue", null, Expression.Constant(iOrdinal)),
                    Expression.Constant(TypeHelper.GetNonNullableType(column.Type), typeof(Type))
        ),
    column.Type
    );

Disappointing because I really like SubSonic/Linq.

In the end we gave up and I wrote this - http://www.toptensoftware.com/petapoco. After porting, our load test showed requests per second went up and CPU load dropped from about 80% to 5%.

Answer 2

I was able to make a HUGE difference in performance by caching the database instance it creates in the constructor/init procedures. What I am seeing now is ~2-3x speed up, depending on the situation and the run.

1) The method of just replacing _db with a static instance works fine if you only call the default constructor, and has all the same speed benefits.

// MyProject.MyDB _db;
// replace with a static instance, and remove the "this." from other lines
static MyProject.MyDB _db = new MyDB();

public MyClass() {
    //_db=new MyProject.MyDB();
    Init();
}

2) I have written a little caching class for the DB entries and am calling that from my ActiveRecord.tt file in all the old places where "new()" was used.

// REPLACE "MyDB" with the name of your DB.  Alternately, include this 
// class in Context.tt and have it generate the correct name.  

class ContextDatabaseCache {        

    public static MyDB GetMyDB()
    {
        return GetInstance("~~default~~", () => new MyDB());
    }

    public static MyDB GetMyDB(string connectionString) {
        return GetInstance(connectionString, () => new MyDB(connectionString));
    }

    public static MyDB GetMyDB(string connectionString, string providerName)
    {
        return GetInstance(connectionString + providerName, () => new MyDB(connectionString, providerName));
    }

    private static Dictionary<string, MyDB> _dict = new Dictionary<string, MyDB>();
    private static MyDB GetInstance(string key, Func<MyDB> createInstance)
    {
        if (!_dict.ContainsKey(key)) {               
            lock (_dict) {
                if (!_dict.ContainsKey(key)) {
                    _dict.Add(key, createInstance());
                }
            }
        }
        return _dict[key];
    }

    ///<summary>Call this when the "DefaultConnection" string changes in the
    ///         App.exe.config file so that a new db instance will be created
    ///         and pick up the changed value. </summary>
    public static void Clear() {
         _dict.Clear();
    }

}

This is the type of replacement that was made in the ActiveRecord.tt file:

public <#=tbl.ClassName#>(){
    _db=new <#=Namespace#>.<#=DatabaseName#>DB();
    Init();            
}

    // becomes this: 
public <#=tbl.ClassName#>(){
    _db= <#=Namespace#>.ContextDatabaseCache.Get<#=DatabaseName#>DB();
    Init();            
}

Answer 3

Apparently this "isn't an issue" with subsonic, though they know it is there. It will NOT be fixed. You have to use a crappy batch query syntax to get this, which no one will.

The thing I don't understand about that is that this is the 90% case. Get a list of records from a table. It should BE the fast one, not the slow one. Everyone does it, everywhere, all the time.

So many problems with subsonic. I had to write caching for the DB field => object field lookups, since they were so damn slow too.