参考了这篇帖子:http://topic.csdn.net/u/20100617/04/80D1BD99-2E1C-4083-AD87-72BF706CB536.html,我对分页代码进行了测试,增加一部分测试内容。我测试的表t_um_ob_gprs_log 共有数据近30W条,有时间我将用此脚本在服务器上测试近亿数据的表。
-
- declare @now datetime
-
- select 'max'方案
- select @now=getdate()
-
- select top 10 * from Student
- where Id>(
- select max(Id)
- from (
- select top 1999990 Id from Student order by Id)tt)
-
- declare @maxDiff int
- select @maxDiff=datediff(ms,@now,getdate())
-
-
- select 'top'方案
- select @now=getdate()
-
- select top 10 * from Student
- where Id not in(select top 1999990 Id from Student)
-
- declare @topDiff int
- select @topDiff=datediff(ms,@now,getdate())
-
-
- select 'row'方案
- select @now=getdate()
-
- select *
- from (
- select row_number()over(order by tc)rn,*
- from (select top 2000000 0 tc,* from Student)t
- )tt
- where rn>1999990
-
- declare @rowDiff int
- select @rowDiff=datediff(ms,@now,getdate())
-
-
- select 'row_number'方案
- select @now=getdate()
-
- select *
- from(
- select top 2000000 row_number()over(order by Id)rn,* from Student
- )t
- where rn>1999990
-
- declare @row_numberDiff int
- select @row_numberDiff=datediff(ms,@now,getdate())
-
-
- select '第20万页'页码,@maxDiff max方案,@topDiff top方案,
- @rowDiff row方案,@row_numberDiff row_number方案
测试结果:(执行了5次)
- 1) 页码 max方案 top方案 row方案 row_number
- 方案
- 第199999 - 200009 126 156 76 76
- 2)页码 max方案 top方案 row方案 row_number
- 方案
- 第199999 - 200009 156 140 96 76
- 3) 页码 max方案 top方案 row方案 row_number
- 方案
- 第199999 - 200009 186 140 93 80
- 4) 页码 max方案 top方案 row方案 row_number
- 方案
- 第199999 - 200009 173 186 96 76
- 5)页码 max方案 top方案 row方案 row_number
- 方案
- 第199999 - 200009 186 143 76 93
可见row_num ber方案还是很有优势的,而两种不同的row_number写法执行时间并没有明显的差距。
同时,也测试了几种不同写法的row_number执行效果:
- declare @now datetime
-
- select 'row_number 1 ' 方案
- select @now=getdate()
- select *from(
- select top 200009 row_number()over(order by c_id)rn,* from t_um_ob_gprs_log
- )t
- where rn>199999
-
-
- declare @row_numberDiff1 int
- select @row_numberDiff1=datediff(ms,@now,getdate())
-
- select 'row_number 2 ' 方案
- select @now=getdate()
- SELECT TOP 10 * FROM (
- SELECT row_number() OVER(ORDER BY c_id) rn,* FROM t_um_ob_gprs_log
- )t
- WHERE rn >199999
-
-
- declare @row_numberDiff2 int
- select @row_numberDiff2=datediff(ms,@now,getdate())
- select 'row_number 3 ' 方案
- select @now=getdate()
- SELECT * FROM (
- SELECT row_number() OVER(ORDER BY c_id) rn,* FROM t_um_ob_gprs_log
- )t
- WHERE rn >=199999 AND rn<=200009
-
-
-
- declare @row_numberDiff3 int
- select @row_numberDiff3=datediff(ms,@now,getdate())
-
- select 'row_number 4 ' 方案
- select @now=getdate()
- select *from (
- select row_number()over(order by tc)rn,*from (select top 200009 0 AS tc,
- * from t_um_ob_gprs_log ORDER BY c_id)t)tt
- where rn>199999
-
-
-
- declare @row_numberDiff4 int
- select @row_numberDiff4=datediff(ms,@now,getdate())
-
-
-
- select '第199999 - 200009' as 页,@row_numberDiff1,@row_numberDiff2,
- @row_numberDiff3,@row_numberDiff4
测试结果:(5次执行时间)
- 1)第199999 - 200009 106 110 80 93
- 2)第199999 - 200009 93 110 93 80
- 3)第199999 - 200009 123 96 76 76
- 4)第199999 - 200009 173 93 76 80
- 5)第199999 - 200009 203 76 80 76
由此可见,后3种写法效率相差无几,第一种有明显的区别。
(michael-zhangyu) |