efficient way to implement paging

Question

Should I use LINQ s Skip   and Take   method for paging  or implement my own paging with a SQL query   Which is most efficient  Why would I choose one over the other   I m using SQL Server 2008  ASP NET MVC and LINQ

User · Answer

In SQL Server 2008   DECLARE  PAGE INTEGER   2 DECLARE  TAKE INTEGER   50  SELECT  t1    FROM       SELECT ROW NUMBER   OVER  ORDER BY  t0   COLUMNORDER  DESC  AS  ROW NUMBER    t0        FROM  dbo   TABLA  AS  t0      WHERE   t0   COLUMNS CONDITIONS    1        AS  t1  WHERE  t1   ROW NUMBER  BETWEEN    PAGE  TAKE  -   TAKE-1   AND   PAGE  TAKE  ORDER BY  t1   ROW NUMBER    In t0 are all records In t1 are only those corresponding to that page

User · Answer

LinqToSql will automatically convert a  Skip N1  Take N2  into the TSQL syntax for you   In fact  every  query  you do in Linq  is actually just creating a SQL query for you in the background   To test this  just run SQL Profiler while your application is running   The skip take methodology has worked very well for me  and others from what I read   Out of curiosity  what type of self-paging query do you have  that you believe is more efficient than Linq s skip take

User · Answer

You can implement paging in this  simple way by passing PageIndex   Declare  PageIndex INT   1 Declare   PageSize INT   20  Select ROW NUMBER   OVER   ORDER BY Products Name ASC    AS RowNumber      Products ID      Products Name into  Result  From Products  SELECT  RecordCount   COUNT    FROM  Results   SELECT    FROM  Results WHERE RowNumber BETWEEN       PageIndex -1     PageSize   1      AND         PageIndex -1     PageSize   1     PageSize  - 1

User · Answer

We use a CTE wrapped in Dynamic SQL  because our application requires dynamic sorting of data server side  within a stored procedure   I can provide a basic example if you d like   I haven t had a chance to look at the T SQL that LINQ produces   Can someone post a sample   We don t use LINQ or straight access to the tables as we require the extra layer of security  granted the dynamic SQL breaks this somewhat    Something like this should do the trick   You can add in parameterized values for parameters  etc   exec sp executesql  WITH MyCTE AS       SELECT TOP  10  ROW NUMBER    OVER      SortingColumn     as RowID  Col1  Col2     FROM MyTable     WHERE Col4     Something     SELECT   FROM MyCTE WHERE RowID BETWEEN 10 and 20

User · Answer

Try using   FROM  TableX  ORDER BY  FieldX  OFFSET 500 ROWS FETCH NEXT 100 ROWS ONLY   to get the rows from 501 to 600 in the SQL server  without loading them in memory  Note that this syntax has become available with SQL Server 2012 only

User · Answer

The approach that I am giving is the fastest pagination that SQL server can achieve  I have tested this on 5 million records  This approach is far better than  OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY  provided by SQL Server   -- The below given code computes the page numbers and the max row of previous page -- Replace  lt  lt  gt  gt  with the correct table data  -- Eg   lt  lt IdentityColumn of Table gt  gt  can be EmployeeId and  lt  lt Table gt  gt  will be dbo Employees  DECLARE  PageNumber int 1  --1st 2nd nth page  In stored proc take this as input param  DECLARE  NoOfRecordsPerPage int 1000    DECLARE  PageDetails TABLE                   lt  lt IdentityColumn of Table gt  gt  int          rownum int           PageNumber  int                            INSERT INTO  PageDetails values 0  0  0          WITH CTE AS                 SELECT  lt  lt IdentityColumn of Table gt  gt   ROW NUMBER   OVER ORDER BY  lt  lt IdentityColumn of Table gt  gt   rownum FROM  lt  lt Table gt  gt                  Insert into  PageDetails         SELECT  lt  lt IdentityColumn of Table gt  gt   CTE rownum  ROW NUMBER   OVER  ORDER BY rownum  as  PageNumber  FROM CTE WHERE CTE rownum  NoOfRecordsPerPage 0   --SELECT   FROM  PageDetails   -- Actual pagination SELECT TOP   NoOfRecordsPerPage  FROM  lt  lt Table gt  gt  AS  lt  lt Table gt  gt  WHERE  lt  lt IdentityColumn of Table gt  gt   gt   SELECT  lt  lt IdentityColumn of Table gt  gt  FROM   PageDetails WHERE PageNumber  PageNumber  ORDER BY  lt  lt Identity Column of Table gt  gt

User · Answer

Trying to give you a brief answer to your doubt  if you execute the skip n  take m  methods on linq  with SQL 2005   2008 as database server  your query will be using the Select ROW NUMBER   Over     statement  with is somehow direct paging in the SQL engine   Giving you an example  I have a db table called mtcity and I wrote the following query  work as well with linq to entities    using  DataClasses1DataContext c   new DataClasses1DataContext          var query    from MtCity2 c1 in c MtCity2s                 select c1  Skip 3  Take 3         Doing something with the query      The resulting query will be   SELECT  t1   CodCity         t1   CodCountry         t1   CodRegion         t1   Name          t1   Code  FROM       SELECT ROW NUMBER   OVER           ORDER BY  t0   CodCity             t0   CodCountry             t0   CodRegion             t0   Name            t0   Code   AS  ROW NUMBER             t0   CodCity             t0   CodCountry             t0   CodRegion             t0   Name            t0   Code      FROM  dbo   MtCity  AS  t0        AS  t1  WHERE  t1   ROW NUMBER  BETWEEN  p0   1 AND  p0    p1 ORDER BY  t1   ROW NUMBER    Which is a windowed data access  pretty cool  btw cuz will be returning data since the very begining and will access the table as long as the conditions are met   This will be very similar to   With CityEntities As        Select ROW NUMBER   Over  Order By CodCity  As Row          CodCity   here is only accessed by the Index as CodCity is the primary     From dbo mtcity   Select  t0   CodCity             t0   CodCountry             t0   CodRegion             t0   Name            t0   Code  From CityEntities c Inner Join dbo MtCity t0 on c CodCity   t0 CodCity Where c Row Between  p0   1 AND  p0    p1 Order By c Row Asc   With the exception that  this second query will be executed faster than the linq result because it will be using exclusively the index to create the data access window  this means  if you need some filtering  the filtering should be  or must be  in the Entity listing  where the row is created  and some indexes should be created as well to keep up the good performance   Now  whats better   If you have pretty much solid workflow in your logic  implementing the proper SQL way will be complicated  In that case LINQ will be the solution   If you can lower that part of the logic directly to SQL  in a stored procedure   it will be even better because you can implement the second query I showed you  using indexes  and allow SQL to generate and store the Execution Plan of the query  improving performance

User · Answer

you can further improve the performance  chech this  From CityEntities c Inner Join dbo MtCity t0 on c CodCity   t0 CodCity Where c Row Between  p0   1 AND  p0    p1 Order By c Row Asc   if you will use the from in this way it will give better result   From   dbo MtCity  t0    Inner Join  CityEntities c on c CodCity   t0 CodCity   reason  because you are using the where class on the CityEntities table which will eliminate many record before joining the MtCity  so 100  sure it will increase the performance many fold     Anyway answer by rodrigoelp is really helpfull   Thanks

User · Answer

While LINQ-to-SQL will generate an OFFSET clause  possibly emulated using ROW NUMBER   OVER   as others have mentioned   there is an entirely different  much faster way to perform paging in SQL  This is often called the  seek method  as described in this blog post here   SELECT TOP 10 first name  last name  score FROM players WHERE  score  lt   previousScore     OR  score    previousScore AND player id  lt   previousPlayerId  ORDER BY score DESC  player id DESC   The  previousScore and  previousPlayerId values are the respective values of the last record from the previous page  This allows you to fetch the  next  page  If the ORDER BY direction is ASC  simply use  gt  instead   With the above method  you cannot immediately jump to page 4 without having first fetched the previous 40 records  But often  you do not want to jump that far anyway  Instead  you get a much faster query that might be able to fetch data in constant time  depending on your indexing  Plus  your pages remain  stable   no matter if the underlying data changes  e g  on page 1  while you re on page 4    This is the best way to implement paging when lazy loading more data in web applications  for instance   Note  the  seek method  is also called keyset paging

User · Answer

In 2008 we cant use Skip   Take    The way is   var MinPageRank    PageNumber - 1    NumInPage   1 var MaxPageRank   PageNumber   NumInPage  var visit   Visita FromSql   SELECT   FROM  SELECT  RANK    ROW NUMBER   OVER  ORDER BY Hora DESC    FROM Visita WHERE   A WHERE A  RANK  BETWEEN  MinPageRank  AND  MaxPageRank    ToList

[sql] efficient way to implement paging

Examples related to sql

Examples related to sql-server

Examples related to asp.net-mvc

Examples related to linq-to-sql

[sql] efficient way to implement paging

Examples related to sql

Examples related to sql-server

Examples related to asp.net-mvc

Examples related to linq-to-sql

Examples related to pagination