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来将整个数据库缓存在内存中?如果否，请购买更多RAM.是否还有其他争用内存的进程，例如IIS/Asp?如果是，请将它们踢出自己的服务器，则应n

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屋！