T-sql:如何执行优化分页? [英] T-sql: how to perform optimised Paging?

问题描述

我编写了以下代码，它工作正常，但如果表包含一百万条记录，则需要 3 秒才能完成.有没有办法优化下面的代码.

I wrote the following code, it works fine, but it takes like 3 sec to complete if the table is containing a million record. Is there a way to optimize the following code.

DBCC DROPCLEANBUFFERS; 
DBCC FREEPROCCACHE;

DECLARE @Page_Size int;
DECLARE @Page_Number int;
DECLARE @Lower_Bound int;
DECLARE @Upper_Bound int;

SET @Page_Size = 30;
SET @Page_Number = 30000;
SET @Lower_Bound = (@Page_Number - 1) * @Page_Size;
--SET @Upper_Bound = @Page_Number * @Page_Size;


WITH Customers AS--(Row_Numbr, Record_Id, First_Name, 
        Middle_Name, Last_Name, Email, Telephone) AS 
(

    SELECT ROW_NUMBER() 
        OVER 
         (ORDER BY Account.Customer.Record_Id) AS Row_Numbr, * 
    FROM Account.Customer 
)

SELECT top(@Page_Size) * 
FROM Customers 
WHERE Row_Numbr > @Lower_Bound-- 
    AND Row_Numbr <= @Upper_Bound -- This is suppose to be faster
--SELECT * FROM Customers 
--WHERE Row_Numbr > @Lower_Bound  
--   AND Row_Numbr <= @Upper_Bound

推荐答案

首先，为什么 DBCC DROPCLEANBUFFERS; ?这是缓冲池的硬冷重置.除非您想测量和调整硬盘的 IO 性能，否则没人会关心冷缓存的性能.这不是您的系统将如何工作.在缓冲池中缓存页面是 数据库中最关键的性能方面，你可以去掉它.这就像出现在没有发动机的法拉利中并询问为什么这么慢.对于性能测量，您应该完全相反:运行查询 4-5 次以预热缓存，然后测量.

First, why DBCC DROPCLEANBUFFERS; ? This is a hard cold reset of the buffer pool. Unless you want to measure and tune your hard drives IO performance, nobody cares about the performance of a cold cache. This is not how your system will work. Caching pages in the buffer pool is the most critical performance aspect in databases, and you take that out. Its like showing up in a Ferrari without the engine and asking why is so slow. For performance measurements you should do exactly the opposite: run he query 4-5 times to warm up the cache, then measure.

第二，你的表结构是什么?表 Account.Customer 表簇索引是否按 Record_id 排序?否则，无论您如何表达 T-SQL，都永远无法获得您想要的性能.

Second, what is your table structure? Is the table Account.Customer table cluster index order by Record_id? If no, you will never get the performance you want, no matter how you express your T-SQL.

最后但并非最不重要的是，你有什么系统?它是否有足够的 RAM 将整个数据库缓存在内存中?如果没有，请购买更多内存.是否有其他进程会争夺内存，例如 IIS/Asp?如果是，将它们踢到自己的服务器上，您应该 n如果性能很重要，永远不要在与 Web 服务器相同的主机上运行数据库.

And last but not least, what system do you have? Does it have enough RAM to cache the entire database in memory? If no, buy more RAM. Are there other processes that compete for memory, like IIS/Asp? If yes, kick them out to their own server, you should never ever run the database on the same host as the web server if performance is important.

对于替代的快速分页，请考虑键集驱动的解决方案:

For an alternative fast paging consider keyset driven solutions:

/* moving up */
SELECT top(@Page_Size) * 
FROM Account.Customer  
WHERE Record_Id > @lastPageRecordId
ORDER BY Record_Id;

/* moving down */
SELECT top(@Page_Size) * 
FROM Account.Customer  
WHERE Record_Id < @firstPageRecordId
ORDER BY Record_Id DESC;

键集驱动的解决方案可以直接搜索到最后一个位置，然后使用聚集索引键位置范围扫描下一页/上一页.分页逻辑(状态)必须记住正在显示的页面上的最后一个和第一个键，以便从那里继续，而不是记住页码.

A keyset driven solution can seek straight to the last position and then range scans the next/previous page, using the clustered index key position. The paging logic (state) must remember the last and first keys on the page being displayed in order to continue from there, instead of remembering the page number.

基于行数的解决方案(以及 MySQL 中的 LIMIT)比基于键集的解决方案效率低，因为它们总是必须计算记录数来定位自己，而不是像键集那样直接寻找位置.

Rowcount based solutions (as well as LIMIT in MySQL) are less efficient than keyset based ones because they always have to count the records to position themselves, instead of seeking straight to the position as keysets can.

这篇关于T-sql:如何执行优化分页?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！