Subsonic linq using activerecord very slow compared to simplerepository
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();
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%.
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();
}
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.
精彩评论