2

I have the following code that takes 5-8 seconds to complete:

recent_items returnedData = (from d in db.recent_items
                             where d.item_number == scannerInput.Text.ToUpper()
                             select d).FirstOrDefault();

Where as the following executes in less than a half second:

string search = scannerInput.Text.ToUpper();

recent_items returnedData = (from d in db.recent_items
                             where d.item_number == search
                             select d).FirstOrDefault();

What in the world is going on?

maccettura
  • 10,514
  • 3
  • 28
  • 35
Joe Ruder
  • 2,122
  • 2
  • 23
  • 52
  • 3
    I guess that's on Entity Framework Core? – Evk May 17 '18 at 14:18
  • 4
    Look at the SQL generated and see how it does affect the query. Generally, ORMs are terrible at building SQL for anything but the most trivial queries. It could also be a missing index in the DB. – Alejandro May 17 '18 at 14:20
  • 1
    I think `ToUpper` forces sql not to use index – Akash Kava May 17 '18 at 14:20
  • related https://stackoverflow.com/questions/12799544/why-does-entity-framework-generate-slow-overengineered-sql – Drag and Drop May 17 '18 at 14:21
  • I removed by comment about `ToUpper()` because it doesn't apply to a field. It should *not* have affected the query. If you are using EF Core before the latest version, EF 2.1 I'd suggest you upgrade. Bugs like that make EF Core 2.1 RC *more* stable than the earlier "stable" versions – Panagiotis Kanavos May 17 '18 at 14:58
  • I am not using core (not in this project at least!) -- Joe – Joe Ruder May 17 '18 at 15:00

1 Answers1

2

The most likely reason for the discrepancy is that EF evaluates the first condition in memory, after retrieving all rows from RDBMS.

Microsoft documentation says the following about Client vs. Server Evaluation:

Entity Framework Core supports parts of the query being evaluated on the client and parts of it being pushed to the database. It is up to the database provider to determine which parts of the query will be evaluated in the database.

Your EF DB provider could not send scannerInput.Text.ToUpper() to RDBMS for evaluation, so it did all comparisons in memory, after retrieving data rows from the database. This decision is correct, because EF DB provider cannot assume that consecutive evaluations of the above expression would yield identical results.

Second query, on the other hand, used a captured variable for the query. The value of that variable is guaranteed to stay constant while EF runs the query, so it proceeded to evaluating the request on the RDBMS side.

Sergey Kalinichenko
  • 714,442
  • 84
  • 1,110
  • 1,523
  • I thought the same, and actually tested this with EF Core and it happily evaluated `myClass.Text.ToUpper()` beforehand and used result as parameter. So, produced identical queries for both cases. So I'm not convinced. Though maybe OP uses different (older) version, who knows. – Evk May 17 '18 at 15:04
  • In addition to that, OP now says in comment that "I am not using core (not in this project at least!)" :) – Evk May 17 '18 at 15:14
  • OP here....I'm not sure where core came in on this. I did not refer to it, I'm sorry for any confusion. One of the first comments was "I guess that's on Entity Framework Core?" -- so maybe that is what sent everybody down that path. – Joe Ruder May 21 '18 at 22:16