实现分页有效途径 [英] efficient way to implement paging

问题描述

我应该使用LINQ的跳过（）和取（）法寻呼或实现自己的分页与一个SQL查询？

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?

我使用的是SQL Server 2008中，ASP.NET MVC和LINQ。

I'm using SQL Server 2008, ASP.NET MVC and LINQ.

推荐答案

想给你一个简短的回答你的疑问，如果执行跳过（N）。取（M）方法（使用SQL 2005/2008作为数据库服务器），您的查询将使用选择ROW_NUMBER（）过... 语句，用的是直接莫名其妙分页中的SQL引擎。

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.

给你一个例子，我有一个叫做db表 mtcity 和我写了下面的查询（工作，以及与LINQ到实体）：

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]

这是一个窗口的数据访问（pretty爽，顺便说一句的Cuz会因为开始时非常地返回数据，并只要满足条件将访问表）。这将是非常相似：

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

就这样，这第二个查询会因为这将使用专门的索引来创建数据访问窗口执行比LINQ结果快除外;这意味着，如果你需要一些过滤，过滤应该是（或必须）在实体上市（其中创建的行）和一些指标应创建并保持了良好的性能。

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.

现在，什么好？

如果你在你的逻辑pretty很多固体的工作流程，实施正确的SQL的方式将复杂的。在这种情况下的LINQ将溶液

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.

如果您可以降低逻辑直接到SQL的那部分（在存储过程）时，它会​​更好，因为你可以实现第二个查询我给你（使用索引），并允许SQL生成和存储执行查询（提高性能）的计划。

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

这篇关于实现分页有效途径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！