Devart Forums

Hi,

How do you page large amounts of records with EF, Linq and Oracle? I tried using Skip and Take, but this takes 15s for a table with 400.000 records. The linq query we use is:

ObjectQuery locaties = this.ObjectContext.LocatieEntity;

ids = locaties.OrderBy(b => b.Id).Skip(skip).Take(pageSize).Select(s => s.Id);

What we would like is the performance of:

select * from (
select id from (select id, rownum rn from inst)
where rn between 100000 and 100009) inst, vst_inst
where inst.id = vst_inst.id

Which takes 0.4s, eventhough that's not ideal either, it's sufficiently fast. Is there a way to achieve this kind of performance through Linq in combination with Oracle?

Is there a way to get more grip on how Linq is translated to Oracle queries via performance annotations?

Also, is there a way to see what the eventual Oracle query is?

Regads,
Jan

I have just replied concerning the performance improvements here.
As for the SQL query tracing, you can use the ObjectQuery.ToTraceString method.

For now we decided to use our handcoded Oracle query to get a list of id's and use this to select the real entities. This seems by far the fastest way to do paging atm. In case you want to look further into this there is a description below of the result we got with the ToTraceString you mentioned.

The ToTraceString helps a lot to identify the problem. Here are 3 queries:
1. The handcoded query in Oracle
2. The query as it is generated by ToTraceString
3. The query with redundant fields removed.


The linq query is:

IQueryable loc =
from locatie in this.ObjectContext.LocatieEntity
select locatie.Id;
loc = loc.OrderBy(b => b).Skip(skip).Take(pageSize);


1. This takes 0.18s

select inst.id from inst,
(select id, rownum rn from inst
order by id) inst2
where inst2.id = inst.id
and rn between 1 and 10
order by rn

2. This takes 8.4s

SELECT
"top".ID AS ID
FROM ( SELECT "Extent1".ID AS ID, "Extent1".MUTATOR_ID AS MUTATOR_ID, "Extent1".CREATOR_ID AS CREATOR_ID, "Extent1".CREATEDATE AS CREATEDATE, "Extent1".MUT_DAT AS MUT_DAT, "Extent1".CONDITIONAL AS CONDITIONAL, "Extent1".UNIEK AS UNIEK, "Extent1".OWNERS_ID AS OWNERS_ID, "Extent1".OWNER_ID AS OWNER_ID, "Extent1".INRICHCODE AS INRICHCODE, "Extent1".DOSSIERNUMMER AS DOSSIERNUMMER, "Extent1".SOORTVESTIGING_ID AS SOORTVESTIGING_ID, "Extent1".SOORTBESTEMMING_ID AS SOORTBESTEMMING_ID, "Extent1".LOCATIE AS LOCATIE, "Extent1".LIGGING_ID AS LIGGING_ID, "Extent1".KVKNUMMER AS KVKNUMMER, "Extent1".NAAM AS NAAM, "Extent1".BEDRIJFSNUMMER AS BEDRIJFSNUMMER, "Extent1".DATUMOPRICHTING AS DATUMOPRICHTING, "Extent1".DATUMBEEINDIGING AS DATUMBEEINDIGING, "Extent1".PERCEEL AS PERCEEL, "Extent1".POSTCODE AS POSTCODE, "Extent1".NR1 AS NR1, "Extent1".NR2 AS NR2, "Extent1".NR3 AS NR3, "Extent1".HUISLIGGING_ID AS HUISLIGGING_ID, "Extent1".STADSDEEL_ID AS STADSDEEL_ID, "Extent1".NAAMWIJK_ID AS NAAMWIJK_ID, "Extent1".WOONPLAATS_ID AS WOONPLAATS_ID, "Extent1".PLAATS_ID AS PLAATS_ID, "Extent1".DOSSIEROMSCHRIJVING AS DOSSIEROMSCHRIJVING, "Extent1".DOSSIERLOCATIE_ID AS DOSSIERLOCATIE_ID, "Extent1".TEAM_ID AS TEAM_ID, "Extent1".DOELGROEP AS DOELGROEP, "Extent1".OPMERKING AS OPMERKING, "Extent1".BISLOCATIE AS BISLOCATIE, "Extent1".OBJECTNUMMER AS OBJECTNUMMER, "Extent1".XCOORDINAAT AS XCOORDINAAT, "Extent1".YCOORDINAAT AS YCOORDINAAT, "Extent1".ZCOORDINAAT AS ZCOORDINAAT, "Extent1".ORGANISATIE AS ORGANISATIE, "Extent1".BRONDOCDATE_AFV AS BRONDOCDATE_AFV, "Extent1".BRONDOCDATE_OPV AS BRONDOCDATE_OPV, "Extent1".BRONDOCNR_AFV AS BRONDOCNR_AFV, "Extent1".BRONDOCNR_OPV AS BRONDOCNR_OPV, "Extent1".IMPORT_DATE AS IMPORT_DATE, "Extent1".IMPORT_ID AS IMPORT_ID, "Extent1".GMISCODE AS GMISCODE, "Extent1".RECORDSTATE AS RECORDSTATE, "Extent1".BURGERSERVICENUMMER AS BURGERSERVICENUMMER, "Extent1".GESLACHTSAANDUIDING AS GESLACHTSAANDUIDING, "Extent1".VOORVOEGSELS AS VOORVOEGSELS, "Extent1".VOORNAMEN AS VOORNAMEN, "Extent1".GESLACHTNAAM AS GESLACHTNAAM, "Extent1".GEBOORTEDATUM AS GEBOORTEDATUM, "Extent1".DATDATUMOVERLIJDEN AS DATDATUMOVERLIJDEN, "Extent1".TELPRIVE AS TELPRIVE, "Extent1".TELWERK AS TELWERK, "Extent1".TELEFAXNUMMER AS TELEFAXNUMMER, "Extent1".EMAIL AS EMAIL, "Extent1".FUNKTIE AS FUNKTIE
FROM ( SELECT "Extent1".ID AS ID, "Extent1".MUTATOR_ID AS MUTATOR_ID, "Extent1".CREATOR_ID AS CREATOR_ID, "Extent1".CREATEDATE AS CREATEDATE, "Extent1".MUT_DAT AS MUT_DAT, "Extent1".CONDITIONAL AS CONDITIONAL, "Extent1".UNIEK AS UNIEK, "Extent1".OWNERS_ID AS OWNERS_ID, "Extent1".OWNER_ID AS OWNER_ID, "Extent1".INRICHCODE AS INRICHCODE, "Extent1".DOSSIERNUMMER AS DOSSIERNUMMER, "Extent1".SOORTVESTIGING_ID AS SOORTVESTIGING_ID, "Extent1".SOORTBESTEMMING_ID AS SOORTBESTEMMING_ID, "Extent1".LOCATIE AS LOCATIE, "Extent1".LIGGING_ID AS LIGGING_ID, "Extent1".KVKNUMMER AS KVKNUMMER, "Extent1".NAAM AS NAAM, "Extent1".BEDRIJFSNUMMER AS BEDRIJFSNUMMER, "Extent1".DATUMOPRICHTING AS DATUMOPRICHTING, "Extent1".DATUMBEEINDIGING AS DATUMBEEINDIGING, "Extent1".PERCEEL AS PERCEEL, "Extent1".POSTCODE AS POSTCODE, "Extent1".NR1 AS NR1, "Extent1".NR2 AS NR2, "Extent1".NR3 AS NR3, "Extent1".HUISLIGGING_ID AS HUISLIGGING_ID, "Extent1".STADSDEEL_ID AS STADSDEEL_ID, "Extent1".NAAMWIJK_ID AS NAAMWIJK_ID, "Extent1".WOONPLAATS_ID AS WOONPLAATS_ID, "Extent1".PLAATS_ID AS PLAATS_ID, "Extent1".DOSSIEROMSCHRIJVING AS DOSSIEROMSCHRIJVING, "Extent1".DOSSIERLOCATIE_ID AS DOSSIERLOCATIE_ID, "Extent1".TEAM_ID AS TEAM_ID, "Extent1".DOELGROEP AS DOELGROEP, "Extent1".OPMERKING AS OPMERKING, "Extent1".BISLOCATIE AS BISLOCATIE, "Extent1".OBJECTNUMMER AS OBJECTNUMMER, "Extent1".XCOORDINAAT AS XCOORDINAAT, "Extent1".YCOORDINAAT AS YCOORDINAAT, "Extent1".ZCOORDINAAT AS ZCOORDINAAT, "Extent1".ORGANISATIE AS ORGANISATIE, "Extent1".BRONDOCDATE_AFV AS BRONDOCDATE_AFV, "Extent1".BRONDOCDATE_OPV AS BRONDOCDATE_OPV, "Extent1".BRONDOCNR_AFV AS BRONDOCNR_AFV, "Extent1".BRONDOCNR_OPV AS BRONDOCNR_OPV, "Extent1".IMPORT_DATE AS IMPORT_DATE, "Extent1".IMPORT_ID AS IMPORT_ID, "Extent1".GMISCODE AS GMISCODE, "Extent1".RECORDSTATE AS RECORDSTATE, "Extent1".BURGERSERVICENUMMER AS BURGERSERVICENUMMER, "Extent1".GESLACHTSAANDUIDING AS GESLACHTSAANDUIDING, "Extent1".VOORVOEGSELS AS VOORVOEGSELS, "Extent1".VOORNAMEN AS VOORNAMEN, "Extent1".GESLACHTNAAM AS GESLACHTNAAM, "Extent1".GEBOORTEDATUM AS GEBOORTEDATUM, "Extent1".DATDATUMOVERLIJDEN AS DATDATUMOVERLIJDEN, "Extent1".TELPRIVE AS TELPRIVE, "Extent1".TELWERK AS TELWERK, "Extent1".TELEFAXNUMMER AS TELEFAXNUMMER, "Extent1".EMAIL AS EMAIL, "Extent1".FUNKTIE AS FUNKTIE, row_number() OVER (ORDER BY "Extent1".ID ASC) AS "row_number"
FROM MAURICE.INST "Extent1"
ORDER BY "Extent1".ID ASC
) "Extent1"
WHERE "Extent1"."row_number" > 0
ORDER BY "Extent1".ID ASC
) "top"
WHERE ROWNUM 0
ORDER BY "Extent1".ID ASC
) "top"
WHERE ROWNUM <= (10)

We wll investigate the situation.
However, EF is well-known for its non-optimal expression trees and queries.
Take a look at this post, it provides a nice illustration.

Judging from your example, the entity you are working with has Defining Query in the SSDL part of the model. It significantly affects the performance because all queries are applied to the subset of fields from the executed defining query (so, first are queried all the fields from the defining query and only then the subset of columns is queried from the result of the query).
The solution is to remove the DefiningQuery element, if it is not necessary in your code.
In case if the query performance is critical for your application we recommend you to use the ExecuteStoreQuery method (it is available in Entity Framework v4 only).