Hi,
I have the following query:
var doorCrossings = await _oracleContext
.DoorCrossings
.AsNoTracking()
.Where(dc => dc.Matricule == user.Matricule && dc.CrossingAt.Date == date.Date)
.OrderBy(dc => dc.CrossingAt)
.ToListAsync();
Nothing special here.
When I execute this with Oracle.EntityFrameworkCore 2.19, I get the following output:
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (46ms) [Parameters=[:user_Matricule_0='04736' (Size = 2000), :date_Date_1='2020-05-30T00:00:00' (DbType = DateTime)], CommandType='Text', CommandTimeout='0']
SELECT "dc"."ID_DOOR_CROSSING", "dc"."SW_ACTIVE", "dc"."ID_DOOR_DBM6K", "dc"."ID_DOOR_CROSSING_CORRECTION", "dc"."DA_CROSSING", "dc"."NM_DOOR", "dc"."NR_MATRICULE", "dc"."CO_DOORSIDE"
FROM "UPERSONNEL"."DOOR_CROSSING" "dc"
WHERE ("dc"."NR_MATRICULE" = :user_Matricule_0) AND ("TRUNC"("dc"."DA_CROSSING") = :date_Date_1)
ORDER BY "dc"."DA_CROSSING" NULLS FIRST
And I get the result pretty quickly.
I tried the same with Oracle.EntityFrameworkCore 3.19 and I get the following output:
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (83,856ms) [Parameters=[:user_Matricule_0='04736' (Size = 2000), :date_Date_1='2020-05-30T00:00:00' (DbType = DateTime)], CommandType='Text', CommandTimeout='0']
SELECT "d"."ID_DOOR_CROSSING", "d"."SW_ACTIVE", "d"."ID_DOOR_DBM6K", "d"."ID_DOOR_CROSSING_CORRECTION", "d"."DA_CROSSING", "d"."NM_DOOR", "d"."NR_MATRICULE", "d"."CO_DOORSIDE"
FROM "UPERSONNEL"."DOOR_CROSSING" "d"
WHERE (("d"."NR_MATRICULE" = :user_Matricule_0) AND (TRUNC("d"."DA_CROSSING") = :date_Date_1))
ORDER BY "d"."DA_CROSSING" NULLS FIRST
Not many difference:
- TRUNC is no longer between quote
- The table alias is not the the one used in the LINQ query
- The parenthesis ordering is a bit different
I get the same result, however, the second query takes waaaaay longer than the first one. It's like 1-2 sec for the first query against 2 minutes for the second one.
The table is pretty huge, however, if the query with 2.19 performs in 2 sec, I expect the query to perform as fast with 3.19.
I tried executing the queries in Oracle SQL Developer tools and the "bad" one performs very well in it.
However, I guess that ":date_Date_1" is not simply passed between quotes to the query and TO_DATE or similar is used and that's where lies the difference (wild guess here).
I tried removing the "AsNoTracking", just for fun, didn't help.
Cheers