SQL 实现分页的有效方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/548475/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
efficient way to implement paging
提问by StoneHeart
Should I use LINQ's Skip()and Take()method for paging, or implement my own paging with a SQL query?
我应该使用 LINQSkip()和Take()方法进行分页，还是使用 SQL 查询实现自己的分页？
Which is most efficient? Why would I choose one over the other?
哪个效率最高？为什么我会选择一个而不是另一个？
I'm using SQL Server 2008, ASP.NET MVC and LINQ.
我正在使用 SQL Server 2008、ASP.NET MVC 和 LINQ。
回答by rodrigoelp
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.
试图给您一个简短的答案，如果您skip(n).take(m)在 linq 上执行方法（使用 SQL 2005 / 2008 作为数据库服务器），您的查询将使用该Select ROW_NUMBER() Over ...语句，在 SQL 引擎中以某种方式直接分页。
Giving you an example, I have a db table called mtcityand I wrote the following query (work as well with linq to entities):
举个例子，我有一个 db 表mtcity，我写了以下查询（与 linq to entity 一起工作）：
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.
例外的是，第二个查询将比 linq 结果执行得更快，因为它将专门使用索引来创建数据访问窗口；这意味着，如果您需要一些过滤，过滤应该（或必须）在实体列表（创建行的位置）中，并且还应该创建一些索引以保持良好的性能。
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.
如果您的逻辑中有非常可靠的工作流，那么实现正确的 SQL 方式将会很复杂。在这种情况下，LINQ 将是解决方案。
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).
如果您可以将这部分逻辑直接降低到 SQL（在存储过程中），那就更好了，因为您可以实现我向您展示的第二个查询（使用索引）并允许 SQL 生成和存储查询（提高性能）。
回答by d.popov
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 2012only
在 SQL 服务器中获取从 501 到 600 的行，而不将它们加载到内存中。请注意，此语法仅适用于SQL Server 2012
回答by Lukas Eder
While LINQ-to-SQL will generate an OFFSETclause (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.
虽然 LINQ-to-SQL 会生成一个OFFSET子句（可能ROW_NUMBER() OVER()像其他人提到的那样模拟使用），但有一种完全不同的、更快的方式在 SQL 中执行分页。这通常称为“搜索方法”，如此处的博客文章中所述。
SELECT TOP 10 first_name, last_name, score
FROM players
WHERE (score < @previousScore)
OR (score = @previousScore AND player_id < @previousPlayerId)
ORDER BY score DESC, player_id DESC
The @previousScoreand @previousPlayerIdvalues are the respective values of the last record from the previous page. This allows you to fetch the "next" page. If the ORDER BYdirection is ASC, simply use >instead.
该@previousScore和@previousPlayerId值是来自前一页的最后一条记录的相应值。这允许您获取“下一个”页面。如果ORDER BY方向是ASC，只需使用>。
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).
使用上面的方法，你不能在没有先获取前 40 条记录的情况下立即跳转到第 4 页。但通常情况下，您无论如何都不想跳那么远。相反，您会得到一个更快的查询，它可能能够在恒定时间内获取数据，具体取决于您的索引。此外，无论基础数据是否发生变化（例如，在第 1 页上，而在第 4 页上），您的页面都将保持“稳定”。
This is the best way to implement paging when lazy loading more data in web applications, for instance.
例如，当在 Web 应用程序中延迟加载更多数据时，这是实现分页的最佳方式。
Note, the "seek method" is also called keyset paging.
请注意，“seek 方法”也称为keyset paging。
回答by mandreko
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.
LinqToSql 会自动将 .Skip(N1).Take(N2) 转换为 TSQL 语法。实际上，您在 Linq 中执行的每个“查询”实际上只是在后台为您创建一个 SQL 查询。要对此进行测试，只需在您的应用程序运行时运行 SQL Profiler。
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?
出于好奇，您认为哪种类型的自分页查询比 Linq 的跳过/获取更有效？
回答by mrdenny
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.
我们在存储过程中使用包含在动态 SQL 中的 CTE（因为我们的应用程序需要对数据服务器端进行动态排序）。如果您愿意，我可以提供一个基本示例。
I haven't had a chance to look at the T/SQL that LINQ produces. Can someone post a sample?
我还没有机会看到 LINQ 生成的 T/SQL。有人可以张贴样品吗？
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).
我们不使用 LINQ 或直接访问表，因为我们需要额外的安全层（允许动态 SQL 在某种程度上打破了这一点）。
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'
回答by ch2o
In SQL Server 2008:
在 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
在 t0 中是所有记录 在 t1 中只有对应于该页面的记录
回答by Rae Lee
You can implement paging in this simple way by passing PageIndex
您可以通过传递 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
回答by Belen Martin
In 2008 we cant use Skip().Take()
在 2008 年我们不能使用 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();
回答by Ali Adravi
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 ，它将提供更好的结果：
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...
原因：因为您正在使用 CityEntities 表上的 where 类，这将在加入 MtCity 之前消除许多记录，因此 100% 肯定它会将性能提高许多倍......
Anyway answer by rodrigoelp is really helpfull.
无论如何，rodrigoelp 的回答真的很有帮助。
Thanks
谢谢
