SubSonic LINQ query is 3 times slower than SubSonic.Query.Select

The module I develop makes lots of small selects, inserts, and updates. Modifications made by commands in SubSonic.Query namespace (ActiveRecord is not my weapon of choice) appear to be much faster than object-by-id select queries written in LINQ.

It takes 7.15s to exe开发者_StackOverflowcute the following LINQ query 1000 times

long a = (
    from u in UserCollection
    where u.UserId == value
    select u.UserId
).FirstOrDefault<long>();

While only 2.38s for the thousand runs of Select query

long a = new SubSonic.Query.Select(provider, "UserId").From<User>()
    .Where<User>(x => x.UserId == value).ExecuteScalar<long>();

I took a time to look under the hood of LINQ in SubSonic. The profiler tells that much of processor time of DbQueryProvider.Execute calls is spent in DbQueryProvider.GetExecutionPlan method - 64 %. 22 % is spent in System.Linq.Expressions.Complie, when DbQueryProvider.Execute uses only 6 % of time.

I'm totally satisfied of how SubSonic LINQ queries are parsed and compiled. However it would be great to have Compilation facility for repeting SubSonic LINQ queries just like System.Data.Linq.CompiledQuery in Linq2Sql.

---

We did some profiling on this too 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%.