Look at the line marked with //***
This will send two batches: 1. get all calendarevents 2 get all needed states problems: 1. it will fetch all calendar events because it looses the .First() 2. it will to a sencond batch to get the state. However it is important to do this in one query with a join because i have a materialized view covering this join. I know of the pattern .Select({ce, ce.DbState}) but the question is: why doesn't the prefetch api do this? It would be kind of a problem to me to migrate all places in my application that are reling on prefetch to the Select solution. This thread was imported from our support forum. The original discussion may contain more detailed answer. |
So to fix the issue #1, you should use this code:
Btw, I'm not sure if .Take will work in this case: likely, you must put .OrderBy before it, since by default result is unordered. I don't remember if we "inject" implicit ordering by PK in such cases, or simply throw an exception on such attempts, so you should check this. But e.g. trying to do the same in SQL (usage of top N w/o order by clause) should lead to failure.
a) Such approach allows to use much more fine-granted control over prefetch process. E.g.:
b) Regular way of prefetching with JOINs has a set of significant disadvantages:
c) Finally, you can prefetch a reference with .Select in DO. The same works for collections as well, but in this case we actually send additional queries during iteration (i.e. this works nearly as in case with regular .Prefetch). Possibly, later we'll use JOIN also for .Select for collection property, but definitely just for the first one (i.e. there will be 1 or 2 additional JOINs at max, dependently if used EntitySet<t> is mapped on intermediate Entity type or not). d) Getting executed an additional batch won't significantly affect on performance:
So short answer is: don't fear .Prefetch. If you'll discover any issue with its performance further, just notify us. I'd like to finish with practical example. Here is a quote from chat with one of our customers: [20:33:41] CB: i am still in shock [20:33:47] CB: however it is what i expected [20:33:56] CB: so all i can say is GOOD JOB GUYS!!!
This query with .Prefetch works 50...100 times faster in DO4 in comparison to EF. Or, more precisely, in our case it simply works as expected, but in case with EF it doesn't. Note that nothing complex is actually written here: the original query is Query.All<owner>().Where(o => o.UserID == userId), everything else are prefetch expressions. Just noticed one more thing: you said Query.All<calendarevent>().Prefetch(x => x.DbState).First() enumerates all CalendarEvents internally. That's not true: try running this on SQL Server with MARS turned on and with varying number of CalendarEvents in the database (e.g. 100K of such items). I suspect you won't notice any changes in performance at all, since internal enumerator of .Prefetch will iterate Query.All<calendarevent>() enumerator (relying on MARS) for some constant number of items only (AFAIK, 128...256 - the number of items it prefetches at once). And since the original query doesn't require any computation (it's just PK enumeration), the actual cost of this query is proportional to the size of this set, i.e. it is constant :) But certainly, version with .Take(...) is faster. |