在做一個(gè)管理全市人口信息系統(tǒng)時(shí),數(shù)據(jù)量大約八百三十萬,測試時(shí)是按照一千萬條數(shù)據(jù)量來的,開始優(yōu)化時(shí)出現(xiàn)各種問題,使用過各種方法,最終優(yōu)化分頁查詢?nèi)魏我豁撛?0秒以內(nèi)
感謝孫偉,個(gè)人稍加修改
-- ============================================= -- Author: <Author,張世民> -- Create date: <Create 2012/05/19> -- Description: <千萬條數(shù)據(jù)分頁查詢優(yōu)化> -- ============================================= -- /*-----存儲過程 分頁處理 孫偉 2005-03-28創(chuàng)建 -------*/ -- /*----- 對數(shù)據(jù)進(jìn)行了2分處理使查詢前半部分?jǐn)?shù)據(jù)與查詢后半部分?jǐn)?shù)據(jù)性能相同 -------*/ ALTER PROCEDURE [ dbo ] . [ GetPageList ] ( @tableName nvarchar ( 200 ), -- --要顯示的表或多個(gè)表的連接 @idField nvarchar ( 150 ), -- --主表的主鍵 @fieldsName nvarchar ( 500 ) = ' * ' , -- --要顯示的字段列表 @pageSize int = 10 , -- --每頁顯示的記錄個(gè)數(shù) @page int = 1 , -- --要顯示那一頁的記錄 @pageCount int = 1 output, -- --查詢結(jié)果分頁后的總頁數(shù) @Counts int = 1 output, -- --查詢到的記錄數(shù) @fieldSort nvarchar ( 200 ) = null , -- --排序字段列表或條件 -- @Sort bit = 0, ----排序方法,0為升序,1為降序(如果是多字段排列Sort指代最后一個(gè)排序字段的排列順序(最后一個(gè)排序字段不加排序標(biāo)記)--程序傳參如:' SortA Asc,SortB Desc,SortC ') @where nvarchar ( 1000 ) = null , -- --查詢條件,不需where @Dist bit = 0 -- --是否添加查詢字段的 DISTINCT 默認(rèn)0不添加/1添加 ) AS SET NOCOUNT ON Declare @sqlTmp nvarchar ( 1000 ) -- --存放動(dòng)態(tài)生成的SQL語句 Declare @strTmp nvarchar ( 1000 ) -- --存放取得查詢結(jié)果總數(shù)的查詢語句 Declare @strID nvarchar ( 1000 ) -- --存放取得查詢開頭或結(jié)尾ID的查詢語句 Declare @strSortType nvarchar ( 10 ) -- --數(shù)據(jù)排序規(guī)則A Declare @strFSortType nvarchar ( 10 ) -- --數(shù)據(jù)排序規(guī)則B Declare @SqlSelect nvarchar ( 50 ) -- --對含有DISTINCT的查詢進(jìn)行SQL構(gòu)造 Declare @SqlCounts nvarchar ( 50 ) -- --對含有DISTINCT的總數(shù)查詢進(jìn)行SQL構(gòu)造 if @Dist = 0 begin set @SqlSelect = ' select ' set @SqlCounts = ' Count(*) ' end else begin set @SqlSelect = ' select distinct ' set @SqlCounts = ' Count(DISTINCT ' + @idField + ' ) ' end /* if @Sort=0 begin */ set @strFSortType = ' ' set @strSortType = ' DESC ' /* end else begin set @strFSortType=' DESC ' set @strSortType=' ASC ' end */ -- ------生成查詢語句-------- -- 此處@strTmp為取得查詢結(jié)果數(shù)量的語句 if @where is null or @where = '' -- 沒有設(shè)置顯示條件 begin set @sqlTmp = @fieldsName + ' From ' + @tableName set @strTmp = @SqlSelect + ' @Counts= ' + @SqlCounts + ' FROM ' + @tableName set @strID = ' From ' + @tableName end else begin set @sqlTmp = + @fieldsName + ' From ' + @tableName + ' where (1>0) ' + @where set @strTmp = @SqlSelect + ' @Counts= ' + @SqlCounts + ' FROM ' + @tableName + ' where (1>0) ' + @where set @strID = ' From ' + @tableName + ' where (1>0) ' + @where end -- --取得查詢結(jié)果總數(shù)量----- exec sp_executesql @strTmp ,N ' @Counts int out ' , @Counts out declare @tmpCounts int if @Counts = 0 set @tmpCounts = 1 else set @tmpCounts = @Counts -- 取得分頁總數(shù) set @pageCount = ( @tmpCounts + @pageSize - 1 ) / @pageSize /* *當(dāng)前頁大于總頁數(shù) 取最后一頁* */ if @page > @pageCount set @page = @pageCount -- /*-----數(shù)據(jù)分頁2分處理-------*/ declare @pageIndex int -- 總數(shù)/頁大小 declare @lastcount int -- 總數(shù)%頁大小 set @pageIndex = @tmpCounts / @pageSize set @lastcount = @tmpCounts % @pageSize if @lastcount > 0 set @pageIndex = @pageIndex + 1 else set @lastcount = @pagesize -- //***顯示分頁 if @where is null or @where = '' -- 沒有設(shè)置顯示條件 begin if @pageIndex < 2 or @page <= @pageIndex / 2 + @pageIndex % 2 -- 前半部分?jǐn)?shù)據(jù)處理 begin if @page = 1 set @strTmp = @SqlSelect + ' top ' + CAST ( @pageSize as VARCHAR ( 4 )) + ' ' + @fieldsName + ' from ' + @tableName + ' order by ' + @fieldSort + ' ' + @strFSortType else begin set @strTmp = @SqlSelect + ' top ' + CAST ( @pageSize as VARCHAR ( 4 )) + ' ' + @fieldsName + ' from ' + @tableName + ' where ' + @idField + ' >(select max( ' + @idField + ' ) from ( ' + @SqlSelect + ' top ' + CAST ( @pageSize * ( @page - 1 ) as Varchar ( 20 )) + ' ' + @idField + ' from ' + @tableName + ' order by ' + @fieldSort + ' ' + @strFSortType + ' ) AS TBMinID) ' + ' order by ' + @fieldSort + ' ' + @strFSortType end end else begin set @page = @pageIndex - @page + 1 -- 后半部分?jǐn)?shù)據(jù)處理 if @page <= 1 -- 最后一頁數(shù)據(jù)顯示 set @strTmp = @SqlSelect + ' *from ( ' + @SqlSelect + ' top ' + CAST ( @lastcount as VARCHAR ( 4 )) + ' ' + @fieldsName + ' from ' + @tableName + ' order by ' + @fieldSort + ' ' + @strSortType + ' ) AS TempTB ' + ' order by ' + @fieldSort + ' ' + @strFSortType else set @strTmp = @SqlSelect + ' * from ( ' + @SqlSelect + ' top ' + CAST ( @pageSize as VARCHAR ( 4 )) + ' ' + @fieldsName + ' from ' + @tableName + ' where ' + @idField + ' <(select min( ' + @idField + ' ) from( ' + @SqlSelect + ' top ' + CAST ( @pageSize * ( @page - 2 ) + @lastcount as Varchar ( 20 )) + ' ' + @idField + ' from ' + @tableName + ' order by ' + @fieldSort + ' ' + @strSortType + ' ) AS TBMaxID) ' + ' order by ' + @fieldSort + ' ' + @strSortType + ' ) AS TempTB ' + ' order by ' + @fieldSort + ' ' + @strFSortType end end else -- 有查詢條件 begin if @pageIndex < 2 or @page <= @pageIndex / 2 + @pageIndex % 2 -- 前半部分?jǐn)?shù)據(jù)處理 begin if @page = 1 set @strTmp = @SqlSelect + ' top ' + CAST ( @pageSize as VARCHAR ( 4 )) + ' ' + @fieldsName + ' from ' + @tableName + ' where 1=1 ' + @where + ' order by ' + @fieldSort + ' ' + @strFSortType else begin set @strTmp = @SqlSelect + ' top ' + CAST ( @pageSize as VARCHAR ( 4 )) + ' ' + @fieldsName + ' from ' + @tableName + ' where ' + @idField + ' >(select max( ' + @idField + ' ) from ( ' + @SqlSelect + ' top ' + CAST ( @pageSize * ( @page - 1 ) as Varchar ( 20 )) + ' ' + @idField + ' from ' + @tableName + ' where (1=1) ' + @where + ' order by ' + @fieldSort + ' ' + @strFSortType + ' ) AS TBMinID) ' + ' ' + @where + ' order by ' + @fieldSort + ' ' + @strFSortType end end else begin set @page = @pageIndex - @page + 1 -- 后半部分?jǐn)?shù)據(jù)處理 if @page <= 1 -- 最后一頁數(shù)據(jù)顯示 set @strTmp = @SqlSelect + ' * from ( ' + @SqlSelect + ' top ' + CAST ( @lastcount as VARCHAR ( 4 )) + ' ' + @fieldsName + ' from ' + @tableName + ' where (1=1) ' + @where + ' order by ' + @fieldSort + ' ' + @strSortType + ' ) AS TempTB ' + ' order by ' + @fieldSort + ' ' + @strFSortType else set @strTmp = @SqlSelect + ' * from ( ' + @SqlSelect + ' top ' + CAST ( @pageSize as VARCHAR ( 4 )) + ' ' + @fieldsName + ' from ' + @tableName + ' where ' + @idField + ' <(select min( ' + @idField + ' ) from( ' + @SqlSelect + ' top ' + CAST ( @pageSize * ( @page - 2 ) + @lastcount as Varchar ( 20 )) + ' ' + @idField + ' from ' + @tableName + ' where (1=1) ' + @where + ' order by ' + @fieldSort + ' ' + @strSortType + ' ) AS TBMaxID) ' + ' ' + @where + ' order by ' + @fieldSort + ' ' + @strSortType + ' ) AS TempTB ' + ' order by ' + @fieldSort + ' ' + @strFSortType end end -- ----返回查詢結(jié)果----- exec sp_executesql @strTmp -- print @strTmp SET NOCOUNT OFF
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯(lián)系: 360901061
您的支持是博主寫作最大的動(dòng)力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點(diǎn)擊下面給點(diǎn)支持吧,站長非常感激您!手機(jī)微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點(diǎn)擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
