RoutePart slower when prefetched? [Lie]

Topics: Core, Writing modules
Jan 5, 2012 at 8:19 PM

Today I made an interesting discovery and I would request a verification whether or not this can be true:

It seems that this:

var items = _contentManager.Query().Where<CommonPartRecord>(r => ids.Contains(r.Id)).List(); // Ids is a list of content item ids
foreach (var item in items )
    ... = item.Value.As<RoutePart>();

is slightly (~5%, but anyway) faster (!) than this:

var items = _contentManager.Query().Where<CommonPartRecord>(r => ids.Contains(r.Id)).Join<RoutePartRecord>().List();
// Later RoutePart fetching the same as previously

The Mvc MiniProfiler (thanks again Pete!) shows that indeed, the first query lacks the join on RoutePartRecords, while the sql query run upon the second content query correctly joins the table. So the latter should be faster, since the records used were eager-loaded, not lazily one-by one. But it's not, and it frustrates me that I don't understand why.

I'm using a recent 1.x changeset, 25e9c3cbb1f2.

Could somebody please enlighten me what kind of sorcery this is?

Jan 5, 2012 at 8:37 PM

Try using QueryHints to force expansion of joined records, see some of Sebastien's recent performance commits for examples.

Jan 5, 2012 at 8:38 PM

Sorry ... just saw you said that SQL profiler showed it was correctly joined ... could just be an indexing issue?

Jan 5, 2012 at 8:50 PM

If your first sample, a query is done to load one content item, then another query to load only a routepart.

If the second example, the first query load one content item, with a join to routepart, just stating it as a constraint. And thus it can just be slower, because it's the same as the first one plus something. This is not doing Eager Load, to have eager load you would need a LEFT JOIN with NH.

To have an eager load, use the WithQueryHints() methods on the query. And when you do the comparison, take into account a scenario with say 10 content items and two parts (common + route for instance). You'll see a true difference.

Jan 5, 2012 at 8:53 PM

Well, it could, I haven't touched the indices (the query is really only this, so no other 3rd party tables are joined). Could it be that querying ~50 RoutePartRecords on-by-one by primary key (I guess this is what the As() does) is faster than joining the RoutePartRecords table on a non-indexed column (ContentItemRecord_Id)? Could be possible but I think my RoutePartRecords table is too small (~130 items) for indices to make a difference here.

Jan 5, 2012 at 8:58 PM

@Sebastien: So in the second example the RoutePartRecords are loaded again, despite previously joined with an inner join? Then I've misunderstood what Join() does here. I'll try the query hints, thanks.

Jan 5, 2012 at 9:37 PM

Actually why is it that query hints can only be used together with content query methods using a record (e.g. join, where)? I'm asking this because GetMany() doesn't require something similar?

Jan 5, 2012 at 9:48 PM

Actually I take back everything I've said. I don't know why, some caching was kicking in previously or what, I don't know, but now the exact same code produces expected results: the lazy-loading one is an order of magnitude slower and it seems that the join has an eager-loading effect.

I don't know what I have measured previously. Sorcery!