EntityFramework - query execution time depends on select order?

Question

I'm noticing strange behavior. I have merchant and order tables and doing two selects one, after other, selects are very simple (select * from merchant, select * from order).

Here is sql profiler trace, when I'm selecting first merchants, then orders:

notice, that orders select is taking whooping 75 seconds (that is for a ~80.000 records, on a really decent machine 8gb ram, ssd, i7).

Now if I change the sequence and select first orders, then merchants:

order query execution time dropped to 2.5 seconds in profiler, but in application it is about the same as in first case (I guess because EF inside tries to bind orders to merchants, as there's foreign key between them).

So question is why profiler sees different times and what EF is doing in second case so long, may be something is configured wrong?

UPDATE: I've started to localize the issue with clean EF model and it works ok. I'm using EF T4 templates to generate context and entity classes, so may be it is outdated and causing problem, will give know if will find something concrete - I think that is somehow related with fixup collections, so looks like SQL profiler was misleading - I guess query was executed ok, just it waited on EF to complete reading results or smth (I mean may be EF is doing something expesive while reading results).

 using (var myEntities = new myEntities())
        {
            var merchants = myEntities.Merchants.ToList();
            var orders = myEntities.Orders.ToList();
        }

Answer 1

The reads are identical: only CPU and duration differs for both sets of data.

In the first iteration, you load data into SQL Server's cache from disk. The 2nd query then reads the 4008 pages from RAM not disk. The same happens for the merchant data but on a lesser scale

Note: the table data and indexes are cached, not the query results. The query will be executed fresh each time. This cache is kept up to date by SQL Server

You've said this is repeatable: so show us the code. There aren't any DBCCs being run that clears the cache? SET options the same?

Answer 2

Duration includes both execution and fetch time. Since in case when merchants are already loaded to context EF has to establish FK during materialization of Order instance, fetch is slower.