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