茄子在线看片免费人成视频,午夜福利精品a在线观看,国产高清自产拍在线观看,久久综合久久狠狠综合

    <s id="ddbnn"></s>
  • <sub id="ddbnn"><ol id="ddbnn"></ol></sub>

  • <legend id="ddbnn"></legend><s id="ddbnn"></s>

    sql server 大量數(shù)據(jù)的分頁(yè)存儲(chǔ)過(guò)程代碼
    來(lái)源:易賢網(wǎng) 閱讀:1288 次 日期:2014-11-14 15:02:32
    溫馨提示:易賢網(wǎng)小編為您整理了“sql server 大量數(shù)據(jù)的分頁(yè)存儲(chǔ)過(guò)程代碼”,方便廣大網(wǎng)友查閱!

    ok,我們首先創(chuàng)建一數(shù)據(jù)庫(kù):data_test,并在此數(shù)據(jù)庫(kù)中創(chuàng)建一表:tb_testtable

    create database data_test --創(chuàng)建數(shù)據(jù)庫(kù)data_test

    go

    use data_test

    go

    create table tb_testtable --創(chuàng)建表

    (

    id int identity(1,1) primary key,

    username nvarchar(20) not null,

    userpwd nvarchar(20) not null,

    useremail nvarchar(40) null

    )

    go

    然后我們?cè)跀?shù)據(jù)表中插入2000000條數(shù)據(jù):

    --插入數(shù)據(jù)

    set identity_insert tb_testtable on

    declare @count int

    set @count=1

    while @count<=2000000

    begin

    insert into tb_testtable(id,username,userpwd,useremail) values(@count,'admin','admin888','lli0077@yahoo.com.cn')

    set @count=@count+1

    end

    set identity_insert tb_testtable off

    我首先寫(xiě)了五個(gè)常用存儲(chǔ)過(guò)程:

    1,利用select top 和select not in進(jìn)行分頁(yè),具體代碼如下:

    create procedure proc_paged_with_notin --利用select top and select not in

    (

    @pageindex int, --頁(yè)索引

    @pagesize int --每頁(yè)記錄數(shù)

    )

    as

    begin

    set nocount on;

    declare @timediff datetime --耗時(shí)

    declare @sql nvarchar(500)

    select @timediff=getdate()

    set @sql='select top '+str(@pagesize)+' * from tb_testtable where(id not in(select top '+str(@pagesize*@pageindex)+' id from tb_testtable order by id asc)) order by id'

    execute(@sql) --因select top后不支技直接接參數(shù),所以寫(xiě)成了字符串@sql

    select datediff(ms,@timediff,getdate()) as 耗時(shí)

    set nocount off;

    end

    2,利用select top 和 select max(列鍵)

    create procedure proc_paged_with_selectmax --利用select top and select max(列)

    (

    @pageindex int, --頁(yè)索引

    @pagesize int --頁(yè)記錄數(shù)

    )

    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>(select max(id) from (select top '+str(@pagesize*@pageindex)+' id from tb_testtable order by id) as temptable)) order by id'

    execute(@sql)

    select datediff(ms,@timediff,getdate()) as 耗時(shí)

    set nocount off;

    end

    3,利用select top和中間變量--此方法因網(wǎng)上有人說(shuō)效果最佳,所以貼出來(lái)一同測(cè)試

    create procedure proc_paged_with_midvar --利用id>最大id值和中間變量

    (

    @pageindex int,

    @pagesize int

    )

    as

    declare @count int

    declare @id int

    declare @timediff datetime

    declare @sql nvarchar(500)

    begin

    set nocount on;

    select @count=0,@id=0,@timediff=getdate()

    select @count=@count+1,@id=case when @count< then id else @id end from tb_testtable order by id

    set @sql='select top '+str(@pagesize)+' * from tb_testtable where id>'+str(@id)

    execute(@sql)

    select datediff(ms,@timediff,getdate()) as 耗時(shí)

    set nocount off;

    end

    4,利用row_number() 此方法為sql server 2005中新的方法,利用row_number()給數(shù)據(jù)行加上索引

    create procedure proc_paged_with_rownumber --利用sql 2005中的row_number()

    (

    @pageindex int,

    @pagesize int

    )

    as

    declare @timediff datetime

    begin

    set nocount on;

    select @timediff=getdate()

    select * from (select *,row_number() over(order by id asc) as idrank from tb_testtable) as idwithrownumber where idrank>@pagesize*@pageindex and idrank<@pagesize*(@pageindex+1)

    select datediff(ms,@timediff,getdate()) as 耗時(shí)

    set nocount off;

    end

    5,利用臨時(shí)表及row_number

    create procedure proc_cte --利用臨時(shí)表及row_number

    (

    @pageindex int, --頁(yè)索引

    @pagesize int --頁(yè)記錄數(shù)

    )

    as

    set nocount on;

    declare @ctestr nvarchar(400)

    declare @strsql nvarchar(400)

    declare @datediff datetime

    begin

    select @datediff=getdate()

    set @ctestr='with table_cte as

    (select ceiling((row_number() over(order by id asc))/'+str(@pagesize)+') as page_num,* from tb_testtable)';

    set @strsql=@ctestr+' select * from table_cte where page_num='+str(@pageindex)

    end

    begin

    execute sp_executesql @strsql

    select datediff(ms,@datediff,getdate())

    set nocount off;

    end

    ok,至此,存儲(chǔ)過(guò)程創(chuàng)建完畢,我們分別在每頁(yè)10條數(shù)據(jù)的情況下在第2頁(yè),第1000頁(yè),第10000頁(yè),第100000頁(yè),第199999頁(yè)進(jìn)行測(cè)試,耗時(shí)單位:ms 每頁(yè)測(cè)試5次取其平均值

    更多信息請(qǐng)查看IT技術(shù)專(zhuān)欄

    更多信息請(qǐng)查看數(shù)據(jù)庫(kù)
    由于各方面情況的不斷調(diào)整與變化,易賢網(wǎng)提供的所有考試信息和咨詢(xún)回復(fù)僅供參考,敬請(qǐng)考生以權(quán)威部門(mén)公布的正式信息和咨詢(xún)?yōu)闇?zhǔn)!

    2026國(guó)考·省考課程試聽(tīng)報(bào)名

    • 報(bào)班類(lèi)型
    • 姓名
    • 手機(jī)號(hào)
    • 驗(yàn)證碼
    關(guān)于我們 | 聯(lián)系我們 | 人才招聘 | 網(wǎng)站聲明 | 網(wǎng)站幫助 | 非正式的簡(jiǎn)要咨詢(xún) | 簡(jiǎn)要咨詢(xún)須知 | 新媒體/短視頻平臺(tái) | 手機(jī)站點(diǎn) | 投訴建議
    工業(yè)和信息化部備案號(hào):滇ICP備2023014141號(hào)-1 云南省教育廳備案號(hào):云教ICP備0901021 滇公網(wǎng)安備53010202001879號(hào) 人力資源服務(wù)許可證:(云)人服證字(2023)第0102001523號(hào)
    云南網(wǎng)警備案專(zhuān)用圖標(biāo)
    聯(lián)系電話(huà):0871-65099533/13759567129 獲取招聘考試信息及咨詢(xún)關(guān)注公眾號(hào):hfpxwx
    咨詢(xún)QQ:1093837350(9:00—18:00)版權(quán)所有:易賢網(wǎng)
    云南網(wǎng)警報(bào)警專(zhuān)用圖標(biāo)