sql server 各種查詢sql語句的分頁儲存過的執行效率

標籤：style   blog   ar   color   sp   on   div   log   bs   

 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)gouse data_testgocreate 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 ondeclare @count intset @count=1while @count<=200000begininsert into tb_testtable (id,username,userpwd,useremail) values(@count,‘admin‘,‘admin888‘,‘[email protected]‘)set @[email protected]+1endset 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 idcreate procedure proc_page_withnotin(@pageIndex int,--頁索引@pageSize int--每頁顯示數)asbeginset 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後不支技直接接參數，所以寫成了字串@sqlselect datediff(ms,@timediff,Getdate()) as wastetimeset 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 idcreate procedure proc_page_withtopmax(@pageIndex int,@pageSize int)asbeginset nocount on;declare @timediff datetimedeclare @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 wastetimeset 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 20create procedure proc_pagewithrownumber(@pageIndex int,@pageSize int)asbeginset nocount on;declare @timediff datetimedeclare @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 wastetimeset nocount off;endexec proc_page_withnotin 2,100000-- wastetime(1533)exec proc_page_withtopmax 2,100000--wastetime(33)exec proc_pagewithrownumber 2,100000--wastetime(1633)

 

sql server 各種查詢sql語句的分頁儲存過的執行效率