sql 分页查询效率比较

举例插入自增长的主键列:

–ALTER table E1T04_2006_TEMP drop column ID
–ALTER table E1T04_2006_TEMP add ID int identity(1,1)
–ALTER TABLE E1T04_2006_TEMP ADD primary key(ID)

—————-

分页查询效率比较结论

在小数据量下(一般应该认为是10万以下,TOP+NOT IN分页方式效率要比ROW_NUMBER()高;在大数据量下(百万级)ROW_NUMBER()分页方式效率要更高一些。

A.首先插入100万条测试数据:

user Stock_new

go
declare @index int
set @index=0
while @index<1000000
begin
insert into Users(sno,[Name]) values(@index,’TEST’)
set @index=@index+1
end

—–

B.接下来先扫盲一下ROW_NUMBER()函数。

ROW_NUMBER()函数是根据参数传递过来的order by子句的值,返回一个不断递增的整数值,也就是它会从1一直不断自增1,直到条件不再满足。例如表Users(Id,Name),使用以下sql语句进行查询:

[sql] view plaincopy

  1. select id,name,row_number() over(order by Id descas rowNum from users where id<10
  2. select id,name,row_number() over(order by Id) as rowNum from users where id<10

两条语句order by排序相反.

 

C.孰优孰劣

以下两种情况,同样取500000到500100中间的数据

1.row_number() over(),语句如下:

declare @time datetime
declare @ms int
set @time= getdate()
select sno,[Name] from (select row_number() over(order by sno) as rowNum,* from T_leamon) as t where rowNum between 500000 and 500100
set @ms=datediff(ms,@time,getdate())
print @ms–毫秒数

 

2.使用TOP加NOT IN方法,语句如下:

declare @time datetime
declare @ms int
set @time= getdate()
select top 100 * from T_leamon where sno not in
(select top 500000 sno from T_leamon order by sno)
order by sno
set @ms=datediff(ms,@time,getdate())
print @ms–毫秒数

Leave a Comment