Take and Skip in SQL Server and EF

Question

I have a ASP.NET MVC 4 application and I'm using EF. I have a table and a SQL view(the view displays rows from this table plus some unimportant data(~ 1000 records)) with ~ 400.000 records. When I display the data in EF it takes 25 seconds

    MVCAppEntities db = new MVCAppEntities();
    public ActionResult Index()
    {
        return View(db.vvItem.OrderBy(n => n.Code).Skip(20).Take(40).ToList());
    }

In SQL Server Management Studio this query takes 0-1 seconds

select  code, quantity, name, price
from (
    select *,
           row_number() over(order by code) as rn
    from vvItem       
 ) as T
where T.rn between 20 and 40

Q : Why is the EF query so slow ? should I make a SP to do the Skip and Take ?

`where T.rn between 20 and 40` is not the same as `Skip(20).Take(40)` — Tim Schmelter, Jun 19 '12 at 14:29
Did you try running SQL profiler to see what SQL query actually gets executed by your EF query? — BrokenGlass, Jun 19 '12 at 14:30
I only want the query to be executed fast. How can I make the EF query to work faster ? — Misi, Jun 19 '12 at 14:32
The SQL query is executed fast, only the EF query is slow. How can I test(and improve) the EF execution time ? — Misi, Jun 19 '12 at 14:42
What is `db` and when / where did you created that instance? — Ladislav Mrnka, Jun 19 '12 at 20:51
I updated my code. db is declared the first time you call the ASP.NET Controller(I'm using EF). — Misi, Jun 20 '12 at 07:10
Are you sure the table is indexed on "code" column? Also in your c# code you have db.gest_vvItem, while in your SQL code you have 'vvItem' — Francisco, Nov 07 '12 at 21:15

score 1 · Answer 1 · answered Jun 20 '12 at 05:03

1

Is it running faster in subsequent calls? It might just be a first time issue. Have a look at this link.

http://www.dotnetspark.com/kb/3706-optimizing-performance.aspx

answered Jun 20 '12 at 05:03

Bishnu Paudel

2,083
1
21
39

Take and Skip in SQL Server and EF

1 Answers1