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

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 2

A couple of thoughts here -

If you need to do 1000 operations, use a BatchQuery instead. It will batch the operations into a single operation and be quite speedy.

I don't know what you did RE Linq to SQL - but if you do SubmitChanges at the very end - that's when the ObjectTracking will kick in and it will appear to be quite fast.

The optimization you're doing here is an interesting one - on one hand it makes perfect sense (thank you for the sleuthing) - on the other your test isn't optimal given what you're trying to do. In other words we've already optimized for this scenario - you would use a different mechanism :).

The differences between AR an SimpleRepo is pretty straightforward - AR has some codegen to allow you a bit of an easier time at development. SimpleRepo is bare bones.

There is a perf hit for using AR - which is magnified by what you're doing here. And again - what you're doing isn't an intended use of AR. It does work, but if you need 1000 pull/writes under 1 second - you won't get it.

The reason we don't cache the DB stuff is that the DB stuff holds connection info and so on. That said - it is T4 so you can do whatever is needed :).

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.