SQL2005 ROW_NUMBER() 的性能如何？

zhuxianzhu 2009-01-19 04:50:25

以下是别人的说法：

SQL Server 2005之后，令人困扰的分页问题似乎有了进展，那就是用ROW_NUMBER()，典型的语句如下SELECT *
FROM
(
SELECT *,ROW_NUMBER() OVER (ORDER BY XXX) AS RowNo
FROM tbl
) AS A
WHERE RowNo >= 11 and RowNo <= 20;
但这里忽略了一个重要的问题，那就是中间那个SELECT语句，实际上相当于做了一个SELECT *，虽然没在结果集看到，但没看到不表示数据库没做。
为什么看起来查询很快，其实是因为后面的RowNo的范围，网上流传的示例RowNo都较小，所以看不出问题，在上例中，SQL Server发现后面的条件之后，中间的SELECT被作了类似TOP 20的处理，也就是只取了20行，所以查询非常快。但把RowNo的查询范围改为10000000到1000010之后，则相当于先TOP 10万行，这时速度就明显慢下来了。
所以说，这个表达式的本质只是先TOP X，再选出X-y,X这个小范围的行，相比以前并没有什么进步。

我想问的是，ROW_NUMBER() 真的性能不那么好吗？ROW_NUMBER()是不是用到了临时表？

...全文

5797 18 打赏收藏转发到动态举报

写回复

用AI写文章

18 条回复

切换为时间正序

请发表友善的回复…

发表回复

haa17 2011-06-10

打赏
举报

mark 学习

fengyqf 2010-12-21

打赏
举报

修改查询语句为如下形式，也是一样

SQL code



    SELECT TOP 页大小 * FROM ( SELECT top 页大小*(页数) ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,col1,col2,col3,col4 FROM table1 ) A WHERE RowNumber > 页大小*(页数-1)

fengyqf 2010-12-21

打赏
举报

对40万条记录的表与2万条记录的两个表inner join 联合查询，应用

SELECT TOP 页大小 * 

FROM 

        (

        SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1

        ) A

WHERE RowNumber > 页大小*(页数-1)

试用结果：比top有很大性能提升，翻页到1万页（10万条）不再死掉，但4万页（40万）左右开始，照样经常死掉。
修改查询语句为如下形式，也是一样

SELECT TOP 页大小 * 

FROM 

        (

        SELECT top 页大小*(页数) ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1

        ) A

WHERE RowNumber > 页大小*(页数-1)

zxdrl 2010-11-09

打赏
举报

联想ThinkPad SL410（28428KC）

sunshift 2009-01-27

打赏
举报

正在研究分页;

yygyogfny 2009-01-20

打赏
举报

学习

zhuxianzhu 2009-01-20

打赏
举报

top的方法也是越往后越慢，

zhuxianzhu 2009-01-20

打赏
举报

谁真正测试过？实际说明问题

lao_bulls 2009-01-20

打赏
举报

顶

viva369 2009-01-19

打赏
举报

[Quote=引用楼主 zhuxianzhu 的帖子:]
以下是别人的说法：

SQL Server 2005之后，令人困扰的分页问题似乎有了进展，那就是用ROW_NUMBER()，典型的语句如下SELECT *
FROM
(
SELECT *,ROW_NUMBER() OVER (ORDER BY XXX) AS RowNo
FROM tbl
) AS A
WHERE RowNo >= 11 and RowNo <= 20;
但这里忽略了一个重要的问题，那就是中间那个SELECT语句，实际上相当于做了一个SELECT *，虽然没在结果集看到，但没看到不表示数据库没做。
为什么看起来查询很快，…
[/Quote]

数据越往后就越慢,还是top的方法比较快

happyflvstone 2009-01-19

打赏
举报

性能问题最后测试说了算

nzperfect 2009-01-19

打赏
举报

[Quote=引用 6 楼 zhuxianzhu 的回复:]
他会不会自动生成一个临时表？
[/Quote]

你可以试一下啊

zhuxianzhu 2009-01-19

打赏
举报

他会不会自动生成一个临时表？

brio8425 2009-01-19

打赏
举报

相信品牌的力量~

水族杰纶 2009-01-19

打赏
举报

--方法1：

--适用于 SQL Server 2000/2005

SELECT TOP 页大小 *

FROM table1

WHERE id NOT IN

          (

          SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id

          )

ORDER BY id

--方法2：

--适用于 SQL Server 2000/2005

SELECT TOP 页大小 *

FROM table1

WHERE id >

          (

          SELECT ISNULL(MAX(id),0) 

          FROM 

                (

                SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id

                ) A

          )

ORDER BY id

--方法3：

--适用于 SQL Server 2005

SELECT TOP 页大小 * 

FROM 

        (

        SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1

        ) A

WHERE RowNumber > 页大小*(页数-1)

--方法四 适用于 SQL Server 2005

DECLARE @pagenum AS INT, @pagesize AS INT 

SET @pagenum = 2 

SET @pagesize = 3 

SELECT * 

FROM (SELECT ROW_NUMBER() OVER(ORDER BY newsid DESC) AS rownum, 

        newsid, topic, ntime, hits 

      FROM news) AS D 

WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize 

ORDER BY newsid DESC

说明，页大小：每页的行数；页数：第几页。使用时，请把“页大小”和“页大小*(页数-1)”替换成数字。

dobear_0922 2009-01-19

打赏
举报

我想问的是，ROW_NUMBER() 真的性能不那么好吗？ROW_NUMBER()是不是用到了临时表？

－－－－－－－－
我测试的结果是ROW_NUMBER()性能很好，分页的时候比用TOP或临时表快。

nzperfect 2009-01-19

打赏
举报

据我测试，百W数据性能还是很好。

dawugui 2009-01-19

打赏
举报

WHERE RowNo >= 11 and RowNo <= 20

取n到m条记录的语句



1.

select top m * from tablename where id not in (select top n id from tablename)



2.

select top m * into 临时表(或表变量) from tablename order by columnname -- 将top m笔插入

set rowcount n

select * from 表变量 order by columnname desc



3.

select top n * from 

(select top m * from tablename order by columnname) a

order by columnname desc





4.如果tablename里没有其他identity列，那么：

select identity(int) id0,* into #temp from tablename



取n到m条的语句为：

select * from #temp where id0 >=n and id0 <= m



如果你在执行select identity(int) id0,* into #temp from tablename这条语句的时候报错,那是因为你的DB中间的select into/bulkcopy属性没有打开要先执行：

exec sp_dboption 你的DB名字,'select into/bulkcopy',true





5.如果表里有identity属性，那么简单：

select * from tablename where identitycol between n and m 



如果是sql server 2005 可以这样写： 

select top 20 * from T order col

except

select top 2 * from T order col