实现分页的有效方法 [英] efficient way to implement paging

问题描述

我应该使用 LINQ 的 Skip() 和 Take() 方法进行分页，还是使用 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.

推荐答案

如果您执行 skip(n).take(m) 方法在linq(使用 SQL 2005/2008 作为数据库服务器)您的查询将使用 Select ROW_NUMBER() Over ... 语句，在 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.

举个例子，我有一个名为 mtcity 的数据库表，我编写了以下查询(也适用于 linq to entity):

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

结果查询将是:

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

除此之外，第二个查询将比 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.

现在，什么更好?

如果您的逻辑中有非常可靠的工作流，那么实现正确的 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屋！