Why oracle entity framework is so slow? 700ms s vs 30 ms with raw SQL

Question

db.Database.SqlQuery<int>(
    "select sum(QUANTITY)   from SomeTable 
    where USER_ID = :userid and Timestamp >= :someDate1and  
    and Timestamp < :someDate2", 
    userId, someDate1, someDate2
)
.First();

this takes 20 ms

var cnt = db.SomeTable.Where(x => 
    x.User.Id == user.Id
    && x.Timestamp >= someDate1
    && x.Timestamp < someDate2
)
.Sum(x => x.Quantity);

This takes freaking 800 ms to execute

I set markers in the code before and after var start = DateTime.UtcNow; and then I log (DateTime.UtcNow - start).TotalMilliseconds.

If I check the generated SQL and execute it on the server directly it takes 2ms. So what is EF spending the rest 798ms for? To fetch one number?

Here is the generated SQL

SELECT "GroupBy1"."A1" AS "C1"
FROM
  (SELECT SUM("Extent1"."QUANTITY") AS "A1"
  FROM "FB"."SomeTable" "Extent1"
  WHERE ((("Extent1"."USER_ID" = :p__linq__0)
  OR (("Extent1"."USER_ID"    IS NULL)
  AND (:p__linq__0            IS NULL)))
  AND ("Extent1"."TIMESTAMP"  >= :p__linq__1)
  AND ("Extent1"."TIMESTAMP"   < :p__linq__2))
  ) "GroupBy1";

Please edit your question to include the SQL query/queries generated by the entity framework. — Progman, Jul 27 '19 at 09:32
What happens if you run the EF query twice in a row? I would assume the first time you run an EF query it will do some kind of setup and assembly/type loading. — ckuri, Jul 27 '19 at 09:36
It's probably the `OR (("Extent1"."USER_ID" IS NULL) AND (:p__linq__0 IS NULL))` that kills it. Your manual SQL does not allow for a null user id. — GSerg, Jul 27 '19 at 09:41
@Toolkit Please provide a [mcve], specially for the case where you execute the same query twice. You might want to check https://stackoverflow.com/questions/13250679/how-to-warm-up-entity-framework-when-does-it-get-cold as there is something called "cold" and "warm" queries, which might have an impact on your performance. — Progman, Jul 27 '19 at 09:51
@Progman where am I supposed to get you a free oracle instance? — Toolkit, Jul 27 '19 at 09:53
@GSerg Wow you are right, I added `x.User.Id != null` and the execution dropped to 15ms. I checked and the `User_Id` is indeed nullable, because there is no `[Required]` attribute on `User` in `SomeTable`. Hm, I guess this is my fault also... But man it is so easy to slip on that road — Toolkit, Jul 27 '19 at 09:55
@Toolkit Yes, that is the price you pay for using an ORM. And sometimes the query kills itself even [when you *don't* do anything wrong](https://stackoverflow.com/questions/23079416/strange-sql-generated-from-linq-when-checking-bit-column#comment75367970_23079416). — GSerg, Jul 27 '19 at 09:59

score 0 · Accepted Answer · answered Jul 27 '19 at 10:00

0

OK so as GSerg pointed the killer is OR (("Extent1"."USER_ID" IS NULL) coming from User_Id being nullable field in Oracle which itself comes from User property missing [Required] attribute. I suppose that's my fault partially, but EF is a slippery slope

answered Jul 27 '19 at 10:00

Toolkit

10,779
8
59
68

1

I've come to the conclusion myself that EF generally just introduces additional complexity and fiendish subtlety. – Steve Jul 27 '19 at 10:23

Why oracle entity framework is so slow? 700ms s vs 30 ms with raw SQL

1 Answers1