Search code examples
c#entity-framework-6oracle-manageddataaccess

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


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";

Solution

  • 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