程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> SQL Server的通用分頁存儲過程 未使用游標,速度更快!

SQL Server的通用分頁存儲過程 未使用游標,速度更快!

編輯:關於SqlServer
使用SQL Server時,分頁處理一直是個比較棘手的問題

正常情況下,SQL Server服務器上會對使用頻率大的Table建立合適的索引
這樣能大幅度的提高數據庫本身的數據檢索速度,建立索引的方法就不細說了

如果需要返回大量數據,從幾百行到幾萬行,甚至幾十萬行數據
這時會發現響應速度越來越慢,甚至發生響應超時的錯誤
為了解決這種大數據量請求的問題,就不得不使用分頁模式了

在這方面,JDBC就強悍得多,它可以將指定的行數和SQL請求一並發送給SQL Server,這樣只返回分頁後的數據,JDBC的原理還不清楚,但在實際使用中,速度還是非常快的

如果沒辦法使用JDBC,最常用的方法就是存儲過程了!

我在寫這個分頁存儲之前,參考了網上的大量相關文章,可以通過關鍵字:SQL Server 分頁 進行搜索
他們主要都是利用SQL中的Top方法,並且對所檢索的數據結構要求有標識列,如果沒有標識列,或者是聯合主鍵,那麼就會非常麻煩了。而且對應用裡原有的SQL檢索部分需要修改的地方較多,工作量較大。

因此,我在寫這個存儲之前就要求一定要對原有的SQL腳本最大程度的兼容

經過一個下午的時間,和我一個同事(絕對是高手)的共同努力下,摸索出了以下的思路:

1、確定存儲的輸入參數:
1)SQL腳本,該參數接收完整的、正確的SQL檢索文本,可將原應用中寫好的SQL腳本直接傳入
2)每頁的數據容量,就是一頁有多少條數據
3)當前頁碼
2、確定分頁機制:
1)執行傳入的SQL腳本,並將結果生成臨時表
2)修改臨時表的結構,增加標識列字段
3)根據標識列字段,計算出指定頁碼內的記錄范圍,並返回
4)返回總數據條數,用於客戶端進行分頁顯示

根據以上的思路,編寫出以下通用的分頁存儲過程:


[code]
--// ============================
--// SQL Server通用分頁存儲過程
--// Author : netwild
--// date : 2010/07/22
--// Email : [email protected]
--// QQ : 52100641(網無忌)
--// ============================

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE PROC execByPage

@sqlQuery varchar(2000), --//輸入參數:SQL檢索語句或表名
@pageSize int, --//輸入參數:每頁顯示記錄條數
@pageIndex int --//輸入參數:當前頁碼

AS

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

declare @tmpTableName varchar(50)
set @tmpTableName = '##TB1516_' + replace(cast(newid() as varchar(40)),'-','') --//生成隨機臨時表名稱

declare @subIndex int
set @subIndex = charindex('from',@sqlQuery)
if (@subIndex > 0)
begin --//帶FROM的標准檢索語句
declare @sqlQuery1 varchar(2000)
declare @sqlQuery2 varchar(2000)
set @sqlQuery1 = substring(@sqlQuery,1,@subIndex - 1)
set @sqlQuery2 = substring(@sqlQuery,@subIndex,len(@sqlQuery))
set @sqlQuery = @sqlQuery1 + ',IDENTITY(numeric,1,1) as ID1516 into ' + @tmpTableName + ' ' + @sqlQuery2
end
else --//不帶FROM的表名
begin
set @sqlQuery = 'select *,IDENTITY(numeric,1,1) as ID1516 into ' + @tmpTableName + ' from' + @sqlQuery
end
exec(@sqlQuery) --//建立並初始化臨時表數據

declare @indexStart varchar(20),@indexEnd varchar(20)
set @indexStart = cast((@pageIndex-1)*@pageSize+1 as varchar(20)) --//數據起始行ID
set @indexEnd = cast(@pageIndex * @pageSize as varchar(20)) --//數據結束行ID

exec('select * from ' + @tmpTableName + ' where ID1516 between ' + @indexStart + ' and ' + @indexEnd) --//檢索該頁數據

exec('select max(ID1516) as recordCount from ' + @tmpTableName) --//提取總條數

exec('drop table ' + @tmpTableName) --//刪除臨時表


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved