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%.