c# - EF sorting & paging - slow as ordered twice? -

c# - EF sorting & paging - slow as ordered twice? -

i have simple entity, , have 100,000 of them in sql sever 2012 database:

public class entity { public int id { get; set; } public string field1 { get; set; } public string field2 { get; set; } }

i want show these in grid, paged 100,000 far many show on 1 screen (and not efficient). grid should allow sorting , filtering - 3 of these operations best done on server, , ef should translate these.

so, lets sec page of 500 sorted field1:

var items = context.entities.orderby(e => e.field1).skip(500).take(500);

when query executed, takes 12 seconds! dug , found it's translated below:

select top (500) [extent1].[id] [id], [extent1].[field1] [field1], [extent1].[field2] [field2] (select [extent1].[id] [id], [extent1].[field1] [field1], [extent1].[field2] [field2], row_number() on (order [extent1].[field1] asc) [row_number] [dbo].[costs] [extent1]) [extent1] [extent1].[row_number] > 500 order [extent1].[field1] asc

surely beingness sorted twice? i'm no sql expert, sub-query orders field1 , assigns order row_number. take top 500 row_numbers on 500 500 rows page 2. don't need order results field1 again.

if take out final order [extent1].[field1] asc, query results seem same , execution time drops circa 150 milliseconds.

so, 150ms preferable 12s - there i'm doing wrong? there can prepare this?

update

the query plan same both. difference on tooltip sort 'actual number of rows' of 4,604 12s query , 1,134 150ms query. add together generated info fixed list of 15 words (for test) - i.e. field1 contains 1 of 15 values, there 15 groups of 6,666 rows.

(click larger image)

sql server 2012 backup

this due bug/idiosyncrasy in sql server when top , gather streams combined. an index prepare it, disabling parallelism (globally, or user, or query). clue gather streams spilled tempdb exceedingly rare condition. http://sqlblog.com/blogs/paul_white/archive/2012/05/03/parallel-row-goals-gone-rogue.aspx 500 level stuff.

note, can't leave out final order by because makes order of results undefined.

c# entity-framework entity-framework-6