查询分页数据和totalcount的最佳性能高效方式是什么?

假设您使用的是MSSQL 2012,则可以使用 Offset and Fetch 极大地清理了服务器端分页。我们发现性能很好,而且在大多数情况下更好。至于获取总列数,只需使用inline下面的window函数。。。它将不包括“offset”和“fetch”所施加的限制。

对于Row_Number,您可以像以前那样使用窗口函数,但我建议您将客户端计算为(pagenumber*pagesize+resultsetRowNumber),因此,如果您在10个结果中的第5页,而在第三行,您将输出第53行。

当应用于一个约有200万个订单的订单表时,我发现以下内容:

快速版本

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

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秒,正是伯爵(*)造成了缓慢。我很惊讶这这么慢,但我怀疑这只是在计算每一行的总数。虽然很干净。

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;

结论

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

我建议有几个直接的策略,并将它们应用于正在进行的高价值查询。

DECLARE @pageNumber INT = 1  , 
        @RowsPerPage INT = 20

SELECT  *
FROM    TableName
ORDER BY Id
        OFFSET ( ( @pageNumber - 1 ) * @RowsPerPage ) ROWS
             FETCH NEXT @RowsPerPage ROWS ONLY;

如果你事先计算一下呢?

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

SELECT @total = count(*)
FROM @table

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

另一种方法是计算 max(line) .检查链接

Return total records from SQL Server when using ROW_NUMBER

UPD:

对于单个查询,请在上面的链接上查看marc_s的答案。

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

@pagenumber=5
@pagesize=5

创建一个通用表表达式并编写如下逻辑

Between ((@pagenumber-1)*(@pagesize))+1 and (@pagenumber *@pagesize)

有很多方法可以实现分页:我希望这些信息对您和其他人有用。

示例1:使用offset fetch next子句。2005年推出

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 = 1
declare @pagesize int = 3

--this is a CTE( common table expression and this is introduce in 2005)
with query as
(
  select ROW_NUMBER() OVER(ORDER BY name ASC) as line, name from @table
) 

--order by clause is required to use offset-fetch
select * from query
order by name 
offset ((@pagenumber - 1) * @pagesize) rows
fetch next @pagesize rows only

示例2:使用row_number()函数并在

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

SELECT *
FROM 
(select ROW_NUMBER() OVER (ORDER BY PRODUCTNAME) AS RowNum, * from Products)
as Prodcut
where RowNum between (((@pagenumber - 1) * @pageSize )+ 1) 
and (@pagenumber * @pageSize )

我希望这些对所有人都有帮助

我不喜欢其他解决方案过于复杂,所以这是我的版本。

一次性执行三个select查询,并使用输出参数获取计数值。此查询返回总计数、筛选器计数和页面行。它支持对源数据进行排序、搜索和筛选。它易于阅读和修改。

假设您有两个具有一对多关系的表,项目及其价格会随着时间的推移而变化,因此示例查询不会太琐碎。

create table shop.Items
(
    Id uniqueidentifier not null primary key,
    Name nvarchar(100) not null,
);

create table shop.Prices
(
    ItemId uniqueidentifier not null,
    Updated datetime not null,
    Price money not null,
    constraint PK_Prices primary key (ItemId, Updated),
    constraint FK_Prices_Items foreign key (ItemId) references shop.Items(Id)
);

以下是查询:

select @TotalCount = count(*) over()
from shop.Items i;

select @FilterCount = count(*) over()
from shop.Items i
outer apply (select top 1 p.Price, p.Updated from shop.Prices p where p.ItemId = i.Id order by p.Updated desc) as p
where (@Search is null or i.Name like '%' + @Search + '%')/**where**/;

select i.Id as ItemId, i.Name, p.Price, p.Updated
from shop.Items i
outer apply (select top 1 p.Price, p.Updated from shop.Prices p where p.ItemId = i.Id order by p.Updated desc) as p
where (@Search is null or i.Name like '%' + @Search + '%')/**where**/
order by /**orderby**/i.Id
offset @SkipCount rows fetch next @TakeCount rows only;

您需要为查询提供以下参数:

• @SkipCount -根据页码计算要跳过的记录数。
• @TakeCount -返回的记录数,根据页面大小计算或等于页面大小。
• @Search -要在某些列中搜索的文本,由网格搜索框提供。
• @TotalCount -数据源中记录的总数,输出参数。
• @FilterCount -搜索和筛选操作后的记录数,输出参数。

您可以替换 /**orderby**/ 如果网格必须支持按列排序,请使用列列表及其排序方向进行注释。您可以从网格中获取这些信息,并将其转换为SQL表达式。最初我们仍然需要按某个列对记录进行排序,我通常使用ID列。

如果网格必须支持按每列单独筛选数据,则可以替换 /**where**/ 带有SQL表达式的注释。

如果用户不搜索和过滤数据,而是只点击网格页面,则此查询根本不会更改,并且数据库服务器会非常快速地执行它。