Why is LinqToEntities Skip/Take oracle implementation so slow

Question

Context is an Oracle Database, Entity Framework 5, LinqToEntities, Database First.

I'm trying to implement pagination over some large tables, my linqToEntities query looks like this :

context.MyDbSet
    .Include("T2")
    .Include("T3")
    .Include("T4")
    .Include("T5")
    .Include("T6")
    .Where(o => o.T3 != null)
    .OrderBy(o => o.Id)
    .Skip(16300)
    .Take(50);

Fact is, depending on how many records i wanna skip (0 or 16300) it goes from 0.08s to 10.7s.

It seems weird to me so i checked the generated SQL and here is how it looks like :

SELECT * 
FROM ( 
SELECT 
[...]
FROM ( SELECT [...] row_number() OVER (ORDER BY "Extent1"."Id" ASC) AS "row_number"
    FROM      T1 "Extent1"
    LEFT OUTER JOIN T2 "Extent2" ON [...]
    LEFT OUTER JOIN T3 "Extent3" ON [...]
    LEFT OUTER JOIN T4 "Extent4" ON [...]
    LEFT OUTER JOIN T5 "Extent5" ON [...]
    LEFT OUTER JOIN T6 "Extent6" ON [...]
    WHERE ("Extent1"."SomeId" IS NOT NULL)
)  "Filter1"
WHERE ("Filter1"."row_number" > 16300)
ORDER BY "Filter1"."Id" ASC
)
WHERE (ROWNUM <= (50) )

I checked if it actually was Oracle who took time through SQL Developper, and it is.

I then checked the execution plan and it's what appears :

Execution Plan

All i actually understand is that there's no STOPKEY for the first filter over row_number and that it probably fetch the whole sub query.

I don't really know where to look at, if i'm not mistaken, the request is generated by ODT/ODP/.. and thus, should be optimized for an Oracle DB.. (and i can't change it myself)

Maybe my database model is rotten and i could add whetever indexes or optimization for it to work better ?

The generated query looks correct, you probably need to index the column in the order by and perhaps more. — Magnus, Jan 22 '14 at 12:48
@Magnus All the columns on which i have a where or order by clause are already indexed. — Florian F., Jan 22 '14 at 16:50
What does the timing look like without all of the `Includes`? — jamesSampica, Jan 23 '14 at 21:19
You can't skip 16000 records and expect performance. Use partitions. — Travis J, Jan 23 '14 at 21:19
@Shoe There's still a difference from 0.03s to 0.08s. Not as big of a difference than with all the joins. — Florian F., Jan 24 '14 at 08:23
@TravisJ How would i implement partitionning if the order of my rows depends on user input ? Do you have any reference for it i could use ? — Florian F., Jan 24 '14 at 08:26

score 2 · Accepted Answer · answered Jan 22 '14 at 19:14

2

You see all those UNIQUE SCAN subqueries? That's doing a UNIQUE on every single one of those tables.

You should have a one-to-many relationship with T1 being the parent, and any tables you need to be in the query to have a FOREIGN KEY relationship to an indexed ID primary key column on T1.

Then you can use Join instead of Include and the subqueries will be unnecessary.

answered Jan 22 '14 at 19:14

Codeman

12,157
10
53
91

All FK are referenced over PKs, PKs are by nature indexed, and FK columns all have an index.Moreover, to my understanding, `UNIQUE SCAN` are the fastest way to access index table (and you can actually confirm that it's not the more expensive part when you check the cost given by explain ?) – Florian F. Jan 23 '14 at 09:17
Nope. UNIQUE SEEK is faster - SCAN has to check every record, SEEK uses the index. http://blog.sqlauthority.com/2007/03/30/sql-server-index-seek-vs-index-scan-table-scan/ – Codeman Jan 23 '14 at 20:56
Then how do i get to having UNIQUE SEEK ? If that's the problem ? How is using Join any different from using Include SQL wise ? How are the subqueries unnecessary (they're implemented for the limit and seems mendatory) ? – Florian F. Jan 24 '14 at 08:26

Why is LinqToEntities Skip/Take oracle implementation so slow

1 Answers1