Pagination over large tables slow #106
Description
There is a significant performance drop in Oracle's adapter for EF Core compared to EF6 when selecting from a table with 2 million rows. EF6 is using a different approach for computing the row number required for paging.
EF6
SELECT *
FROM (
SELECT
"Extent1"."Id" AS "Id"
FROM ( SELECT "Extent1"."Id" AS "Id", row_number() OVER (ORDER BY "Extent1"."Id" DESC) AS "row_number"
FROM "Table" "Extent1"
) "Extent1"
WHERE ("Extent1"."row_number" > :p__linq__0)
ORDER BY "Extent1"."ID" DESC
)
WHERE (ROWNUM <= (:p__linq__1))
EF Core
Select
K0 "Id"
from (
select
"m2".*,
rownum r2
from
(
SELECT "t"."Id" K0
FROM "Table" "t"
ORDER BY "t"."Id" DESC
) "m2"
) "m1"
where r2 > :p_0
and r2 <= (:p_0 + :p_1)
I am using Oracle 11g (11.2.0.3.0) and have set compatibility level to 11 when configuring the DbContext.