具有分页和计数的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应用程序要求显示页码.例如，如果a在数据库中有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 and Fetch大大清理服务器端分页.我们发现性能很好，并且在大多数情况下更好.至于获得总列数，只需使用inline下面的window函数...它将不包括'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屋！