带有分页和计数的 SQL Server 查询 [英] SQL Server query with pagination and count

问题描述

我想用分页进行数据库查询.所以，我使用了一个公用表表达式和一个排名函数来实现这一点.看看下面的例子.

I want to make a database query with pagination. So, I used a common-table expression and a ranked function to achieve this. Look at the example below.

declare @table table (name varchar(30));
insert into @table values ('Jeanna Hackman');
insert into @table values ('Han Fackler');
insert into @table values ('Tiera Wetherbee');
insert into @table values ('Hilario Mccray');
insert into @table values ('Mariela Edinger');
insert into @table values ('Darla Tremble');
insert into @table values ('Mammie Cicero');
insert into @table values ('Raisa Harbour');
insert into @table values ('Nicholas Blass');
insert into @table values ('Heather Hayashi');

declare @pagenumber int = 2;
declare @pagesize int = 3;
declare @total int;

with query as
(
    select name, ROW_NUMBER() OVER(ORDER BY name ASC) as line from @table
)
select top (@pagesize) name from query
    where line > (@pagenumber - 1) * @pagesize

在这里，我可以指定@pagesize 和@pagenumber 变量来给我我想要的记录.但是，此示例(来自存储过程)用于在 Web 应用程序中进行网格分页.此 Web 应用程序需要显示页码.例如，如果数据库中有 12 条记录且页面大小为 3，那么我必须显示 4 个链接，每个链接代表一个页面.

Here, I can specify the @pagesize and @pagenumber variables to give me just the records that I want. However, this example (that comes from a stored procedure) is used to make a grid pagination in a web application. This web application requires to show the page numbers. For instance, if a have 12 records in the database and the page size is 3, then I'll have to show 4 links, each one representing a page.

但是如果不知道有多少条记录，我就无法做到这一点，这个例子只给了我记录的子集.

But I can't do this without knowing how many records are there, and this example just gives me the subset of records.

然后我把存储过程改成返回count(*).

Then I changed the stored procedure to return the count(*).

declare @pagenumber int = 2;
declare @pagesize int = 3;
declare @total int;
with query as
(
    select name, ROW_NUMBER() OVER(ORDER BY name ASC) as line, total = count(*) over()from @table
)
select top (@pagesize) name, total from query
    where line > (@pagenumber - 1) * @pagesize

因此，与每一行一起，它将显示记录总数.但我不喜欢它.

So, along with each line, it will show the total number of records. But I didn't like it.

我的问题是是否有更好的方法(性能)来做到这一点，也许设置 @total 变量而不在 SELECT 中返回此信息.或者这个总列不会对性能造成太大影响?

My question is if there's a better way (performance) to do this, maybe setting the @total variable without returning this information in the SELECT. Or is this total column something that won't harm the performance too much?

谢谢

推荐答案

假设您使用的是 MSSQL 2012，您可以使用 Offset 和 Fetch，它极大地清理了服务器端分页.我们发现性能很好，而且在大多数情况下更好.至于获得总列数，只需使用内联下方的窗口函数......它不会包括'offset'和'fetch'施加的限制.

Assuming you are using MSSQL 2012, you can use Offset and Fetch which cleans up server-side paging greatly. We've found performance is fine, and in most cases better. As far as getting the total column count, just use the window function below inline...it will not include the limits imposed by 'offset' and 'fetch'.

对于 Row_Number，您可以按照您的方式使用窗口函数，但我建议您将该客户端计算为 (pagenumber*pagesize + resultsetRowNumber)，因此如果您位于 10 个结果的第 5 页并且在第三行，您将输出第 53 行.

For Row_Number, you can use window functions the way you did, but I would recommend that you calculate that client side as (pagenumber*pagesize + resultsetRowNumber), so if you're on the 5th page of 10 results and on the third row you would output row 53.

当应用于包含大约 200 万个订单的 Orders 表时，我发现以下内容:

When applied to an Orders table with about 2 million orders, I found the following:

快速版本

这在不到一秒钟的时间内运行了.它的好处是您可以在公共表表达式中进行一次过滤，它既适用于分页过程，也适用于计数.当 where 子句中有许多谓词时，这会使事情变得简单.

This ran in under a second. The nice thing about it is that you can do your filtering in the common table expression once and it applies both to the paging process and the count. When you have many predicates in the where clause, this keeps things simple.

declare @skipRows int = 25,
        @takeRows int = 100,
        @count int = 0

;WITH Orders_cte AS (
    SELECT OrderID
    FROM dbo.Orders
)

SELECT 
    OrderID,
    tCountOrders.CountOrders AS TotalRows
FROM Orders_cte
    CROSS JOIN (SELECT Count(*) AS CountOrders FROM Orders_cte) AS tCountOrders
ORDER BY OrderID
OFFSET @skipRows ROWS
FETCH NEXT @takeRows ROWS ONLY;

慢版

这花了大约 10 秒，导致缓慢的是 Count(*).我很惊讶这太慢了，但我怀疑它只是在计算每一行的总数.不过它很干净.

This took about 10 sec, and it was the Count(*) that caused the slowness. I'm surprised this is so slow, but I suspect it's simply calculating the total for each row. It's very clean though.

declare @skipRows int = 25,
@takeRows int = 100,
@count int = 0


SELECT 
    OrderID,
    Count(*) Over() AS TotalRows
FROM Location.Orders
ORDER BY OrderID
OFFSET @skipRows ROWS
FETCH NEXT @takeRows ROWS ONLY;

结论

我们之前经历过这个性能调优过程，实际上发现它取决于查询、使用的谓词和涉及的索引.例如，我们第二次引入了一个视图，所以我们实际上查询了基表，然后连接了视图(包括基表)，它实际上表现得非常好.

We've gone through this performance tuning process before and actually found that it depended on the query, predicates used, and indexes involved. For instance, the second we introduced a view it chugged, so we actually query off the base table and then join up the view (which includes the base table) and it actually performs very well.

我建议您采用一些直接的策略，并将它们应用到出现问题的高价值查询中.

I would suggest having a couple of straight-forward strategies and applying them to high-value queries that are chugging.

这篇关于带有分页和计数的 SQL Server 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！