Has anyone noticed that EF Core 1.0 2015.1 has made the queries very inefficient

Question

After upgrading to Asp.Net Core 2015.1 I have noticed that a lot of EF queries have become a lot slower to run.

I have done some investigation and found that a lot of the queries with where filters now get evaluated in Code, rather than passing the filters to SQL as part of a where clause to run with the query.

We ended up having to re-write a number of our queries as stored procedures to get back performance. Note these used to be efficient prior to the 2015.1 release. Something obviously was changed, and a lot of queries are doing select all queries on a table and then filtering the data in code. This approach is terrible for performance, e.g. reading a table with lots of rows, to filter everything but maybe 2 rows.

I have to ask what changed, and whether anyone else is seeing the same thing?

For example: I have a ForeignExchange table along with a ForeignExchangeRate table which are linked via ForeignExchangeid = ForeignExchangeRate.ForeignExchangeId

await _context.ForeignExchanges
                .Include(x => x.ForeignExchangeRates)
                .Select(x => new ForeignExchangeViewModel
                {
                    Id = x.Id,
                    Code = x.Code,
                    Name = x.Name,
                    Symbol = x.Symbol,
                    CountryId = x.CountryId,
                    CurrentExchangeRate = x.ForeignExchangeRates
                        .FirstOrDefault(y => (DateTime.Today >= y.ValidFrom) 
                                          && (y.ValidTo == null || y.ValidTo >= DateTime.Today)).ExchangeRate.ToFxRate(),
                    HistoricalExchangeRates = x.ForeignExchangeRates
                        .OrderByDescending(y => y.ValidFrom)
                        .Select(y => new FxRate
                        {
                            ValidFrom = y.ValidFrom,
                            ValidTo = y.ValidTo,
                            ExchangeRate = y.ExchangeRate.ToFxRate(),
                        }).ToList()

                })
                .FirstOrDefaultAsync(x => x.Id == id);

And I use this to get the data for editing a foreign exchange rate

So the SQL generated is not as expected. It generates the following 2 SQL statements to get the data

SELECT TOP(1) [x].[ForeignExchangeId], [x].[ForeignCurrencyCode], [x].[CurrencyName], [x].[CurrencySymbol], [x].[CountryId], (
SELECT TOP(1) [y].[ExchangeRate]
FROM [ForeignExchangeRate] AS [y]
WHERE ((@__Today_0 >= [y].[ValidFrom]) AND ([y].[ValidTo] IS NULL OR ([y].    [ValidTo] >= @__Today_1))) AND ([x].[ForeignExchangeId] = [y].[ForeignExchangeId])
)FROM [ForeignExchange] AS [x]
WHERE [x].[ForeignExchangeId] = @__id_2

and

SELECT [y0].[ForeignExchangeId], [y0].[ValidFrom], [y0].[ValidTo], [y0].[ExchangeRate]
FROM [ForeignExchangeRate] AS [y0]
ORDER BY [y0].[ValidFrom] DESC

The second query is the one that causes the slowness. If the table has many rows, then it essentially gets the whole table and filters the data in code

This has changed in the latest release as this used to work in the RC versions of EF

One other query I used to have was the following

         return await _context.CatchPlans
            .Where(x => x.FishReceiverId == fishReceiverId
                     && x.FisherId == fisherId
                     && x.StockId == stockId
                     && x.SeasonStartDate == seasonStartDate
                     && x.EffectiveDate >= asAtDate
                     && x.BudgetType < BudgetType.NonQuotaed)
            .OrderBy(x => x.Priority)
            .ThenBy(x => x.BudgetType)
            .ToListAsync();

and this query ended up doing a Table read (the entire table which was in the tens of thousands of rows) to get a filter subset of between 2 and 10 records. Very inefficient. This was one query I had to replace with a stored procedure. Reduced from approx 1.5-3.0 seconds down to milliseconds. And note this used to run efficiently before the upgrade

Answer 1

This is a known issue on EF core 1.0.The solution right now is to convert all your critical queries to sync one.The problem is on Async queries right now.They'll sort out this issue on EF core 1.1.0 version.But it has not been released yet.

Here is the Test done by the EF core dev team member :

You can find out more info here :EF Core 1.0 RC2: async queries so much slower than sync

Another suggestion I would like to do.That is try your queries with .AsNoTracking().That too will improve the query performance.

.AsNoTracking()

Sometimes you may want to get entities back from a query but not have those entities be tracked by the context. This may result in better performance when querying for large numbers of entities in read-only scenarios.