efficient way to make paging on sql server 2008

Question

hi there, i'm using sql server 2008, asp.net mvc and linq. Should i use LINQ skip and take method for paging, or do my self paging sql query script which way more efficient than

Answer 1

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?

Answer 2

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).

Answer 3

Hey Stoneheart, I am new in this (stack overflow thing) so I ask for apologies way ahead if the answer isn't clear enough :P

Trying to give you a breif 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 a sample, 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 returing 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 execute faster than the linq result because 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) will be even better because you can implement the second query I showed you (using indexes) and allowing SQL to generate and store the Execution Plan of the query (improving performance).

Cheers and let me know if this worked for you.