EF Core 1.0 RC2: async queries so much slower than sync

[ComptonAlvaro]

One of the features that I have missed in EF6 it was the possibility to use includes with raw quieries. This is possible with EF Core, something like this:

await myDbContext.MyDbSet.FromSql("Select * from Mytable")
.Include(x=> x.Property1.Property2.Property6)
.Include(x=> x.Property3.Property4.Property5)
.Include(x=> x.Property7.Property7)
.ToListAsync();

The problem is that this query it is very slow, about 5 minutes. When I use LinQ the time is slower, much slower, but in my case I wanted to use raw quieries because I want to use the hints of Sql Server to block some rows.

I am using Express Profiler to get the query that EF Core send to Sql Server, and if I run this query in Sql Server Management Studio, it takes just 3 seconds to complete. So the problem it seems to be in EF Core.

Also, with Express Profiler I see that the query makes 370000 reads that takes 310000 seconds (about 5 minutes). But I don't understand this because in SSMS takes 3 seconds this query.

I have tried this query:

await myDbContext.MyDbSet.FromSql("Select * from Mytable")
.ToListAsync();

This takes 3 seconds, but if I try this one:

await myDbContext.MyDbSet.FromSql("Select * from Mytable")
.Include(x=> x.Property1.Property2)
.ToListAsync();

It takes 77 seconds.

With EF6, the same query with all the includes takes 3 secods, and I am using a IQueryable that has to create the t-sql query. I have expected that EF Core would have a better performace than EF6, at least not so bad. The problem with EF6 is that if I want to use the hints of Sql Server I have to do two queries, one with the raw sql to block the rows and another query, with LinQ, to bring the entities to the dbContext and the includes.

Anyway, if I use the same query with LinQ with EF Core, it takes a bit more, about 4 seconds, so it has worse performace.

Really I don't know if I am doing something wrong or EF Core with the includes has worse performance than EF6. And with raw query with includes it is very bad.

I guess that EF Core has problem populating the navigation properties, because really the t-sql that send to the database is very fast, the total time it is very big.

Thanks.

[ajcvickers]

@ComptonAlvaro Can you post the code for the query in EF6? (Given that EF6 does not have FromSql it cannot be exactly the same.)

[ComptonAlvaro]

Ok, in the case of EF Core with raw sql and includes, I missed a condition in the where so with the same conditions in the query with LinQ and includes and raw sql and includes, the time is the same, about 3 seconds. However, with EF6, in this case I only can try LinQ query, and the same includes, the time is about 1.5 seconds.

The code that I am using in EF Core:
First query:

IQueryable<TableA> iqTableA = miDbContext.TableA
    .Include(x => x.TableB).ThenInclude(y => y.TableC)
    .Include(x => x.PropertyA).ThenInclude(y => y.PropertyA-B).ThenInclude(z => z.PropertyA-C)
    .Include(x => x.PropertyB).ThenInclude(y => y.Property-B-B).ThenInclude(z => z.PropertyB-C);

    List<TableA> myLst = iqTableA.ToListAsync();

Second query:

List<TableA> miLst = await miDbContext.TableA
        .FromSql("Select * from TableA")
        .Include(x => x.TableB).ThenInclude(y => y.TableC)
        .Include(x => x.PropertyA).ThenInclude(y => y.PropertyA-B).ThenInclude(z => z.PropertyA-C)
        .Include(x => x.PropertyB).ThenInclude(y => y.Property-B-B).ThenInclude(z => z.PropertyB-C);
        .ToListAsync();

The Query in EF6 is:

IQueryable<TableA> iqTableA = miDbContext.TableA
    .Include(x => x.TableB.TableC)
    .Include(x => x.PropertyA.PropertyA-B.PropertyA-C)
    .Include(x => x.PropertyB.Property-B-BPropertyB-C);

    List<TableA> myLst = iqTableA.ToListAsync();

I think that this is the code that you wanted to see. If I am wrong, tell me and I post the correct code.

Thanks so much for your attention.

[gdoron]

@ComptonAlvaro had I had to guess, I would say it's related to this #5808.
Did you check the output SQL queries?

[ComptonAlvaro]

Yes, I have check the two queries and the are the same, and the execute plan is exactly the same. So I gues that the problem is with EF Core, perhaps in the way that it populates the navigation properties.

Because I have tried to separate the query in three simplier queries and the time is the same.

[anpete]

@ComptonAlvaro Just to confirm, you are observing that the time to execute the SQL is the same between EF6 and Core, but the time spent processing the results in EF is slower in Core? Can you run your same experiments with sync instead of async and report the results?

[ComptonAlvaro]

@anpete

I am doing a few more tests. This is the results.
In both cases, I am using LinQ, not raw sql and includes in the case of EF Core, because I know that the time is the same in both cases.

I have done 2 quieries per case, because the first run, in cold, is a bit slower. Also, with express profiler I can get the sql query time and with a stopwatch in my code I can get the total time.

EF Core sync
First run: query time 531ms; total 5242ms
Second run: query time 408ms; total 546ms

EF Core async:
First run: query time 10151ms; total 14666ms
Second run: query time 9600ms; total 9800ms

EF6 sync
First run: query time 1137ms; total 1472ms
Second run: query time 1133ms; total 1156ms

EF6 async
First run: query time 1504ms; total 1800ms
Second run: query time 1397ms; total 1400ms

EF Core sync is the faster, more than EF6, but in the async version it is the slowest. In the case of EF6, both, async and sync have the same performance. So the work in the sync version of EF Core s very good, but the async version is much worse.

Thanks.

[anpete]

Thanks @ComptonAlvaro. Could you please post the SQL as well?

[gdoron]

@ComptonAlvaro would interesting to see the results when taking the SQL and using it with FromSql.

BTW, how many rows are returned by the query?
Make sure the garbage collection doesn't effect the tests.

[ComptonAlvaro]

I have created and application to test EF6 and EF Core. The results are:

I have attached here the source code. You can find a project in .net 4.6.1 in VS2015 and a script to create the tables and insert the data in a Sql Server database.

TestEFCore.zip

I hope this cna help you, anyway, I will help you in all you need.

Thanks so much.

[anpete]

Thanks @ComptonAlvaro!

[anpete]

Here are my numbers running in Release: