create database data_test
on primary
(
name='data_test_data',
filename='C:\data_test_data.mdf',
size=5mb,
maxsize=100mb,
filegrowth=15%
)
log on
(
name='data_test_log',
filename='C:\data_test_log.ldf',
size=2mb,
filegrowth=1mb
)
go
use data_test
go
create table tb_testtable
(
id int identity (1,1) primary key,
username nvarchar(20) not null,
userpwd nvarchar(20) not null,
userEmail nvarchar(40) null
)
set identity_insert tb_testtable on
declare @count int
set @count=1
while @count<=200000
begin
insert into tb_testtable (id,username,userpwd,useremail) values(@count,'admin','admin888','lihfei89@163.com')
set @count=@count+1
end
set identity_insert tb_testtable off
--利用select top and select not in 耗时1533s
--select top 10 * from tb_testtable where (id not in(select top 15 id from tb_testtable order by id asc)) order by id
create procedure proc_page_withnotin
(
@pageIndex int,--页索引
@pageSize int--每页显示数
)
as
begin
set nocount on;
declare @timediff datetime --消耗时间
declare @sql nvarchar(500)
select @timediff =Getdate()
set @sql = 'select top ' +str(@pageSize) +' * from tb_testtable where (id not in (select top ' + str((@pageIndex-1) * @pageSize) +' id from tb_testtable order by id)) order by id'
execute(@sql) --因select top后不支技直接接参数，所以写成了字符串@sql
select datediff(ms,@timediff,Getdate()) as wastetime
set nocount off;
end
--利用select top and select max耗时33s
--select top 10 * from tb_testtable where
--(id > (select max(id) from (select top 10 id from tb_testtable order by id ) as temp ))
--order by id
create procedure proc_page_withtopmax
(
@pageIndex int,
@pageSize int
)
as
begin
set nocount on;
declare @timediff datetime
declare @sql nvarchar(500)
select @timediff=getDate()
set @sql='select top 10 * from tb_testtable where (id> (select max(id) from (select top '+str((@pageIndex-1)*@pageSize)+' id from tb_testtable order by id) as temp)) order by id'
execute(@sql)
select datediff(ms,@timediff,getdate()) as wastetime
set nocount off;
end
--利用Row_number()耗时1633s
--select * from (select *,row_number() over(order by id) rn from tb_testtable) as temp where rn between 11 and 20
create procedure proc_pagewithrownumber
(
@pageIndex int,
@pageSize int
)
as
begin
set nocount on;
declare @timediff datetime
declare @sql nvarchar(500)
select @timediff=getdate()
set @sql='select * from (select *,row_number() over (order by id) rn from tb_testtable) as temp where rn between '+str((@pageIndex-1)*@pageSize +1 )+' and ' +str(@pageIndex*@pageSize)
execute(@sql)
select datediff(ms,@timediff,getdate()) as wastetime
set nocount off;
end
exec proc_page_withnotin 2,100000-- wastetime(1533)
exec proc_page_withtopmax 2,100000--wastetime(33)
exec proc_pagewithrownumber 2,100000--wastetime(1633)
