公用ms sql 分頁預存程序

仲介交易 HTTP://www.aliyun.com/zixun/aggregation/6858.html">SEO診斷 淘寶客 雲主機 技術大廳

在編寫分頁預存程序前我們先為資料庫創建一個測試表，這個測試表明叫做order，當中有3個欄位，分別是or_id,orName,dateSta;下面創建表腳本：

CREATE TABLE [dbo]. [Orders] (
    [or_id] [int] IDENTITY(1,1) NOT Null,
    [orName] [Nvarchar](50) NOT Null,     [dateSta] [datetime] NOT Null,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
&nbs p;   [or_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALL OW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedpr operty @name=N'MS_Deｓｃｒｉｐｔion', @value=N'寫入時間' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@ level1name=N'Orders', @level2type=N'COLUMN',@level2name=N'dateSta'
GO
SET IDENTITY_INSERT [dbo].[ Orders] ON
INSERT [dbo].[ Orders] ([or_id], [orName], [dateSta]) VALUES (1, N'naoqiu.com', CAST(0x0000A03700FEF029 AS DateTime))
INSERT [dbo].[ Orders] ([or_id], [orName], [dateSta]) VALUES (2, N'naoqiu.com', CAST(0x0000A03700FF5BE8 AS DateTime))
INSERT [dbo]. [Orders] ([or_id], [orName], [dateSta]) VALUES (3, N'押金', CAST(0x0000A03700FF5BF6 AS DateTime))
INSERT [dbo].[ Orders] ([or_id], [orName], [dateSta]) VALUES (4, N'費用', CAST(0x0000A03700FF5BF6 AS DateTime))
INSERT [dbo].[ Orders] ([or_id], [orName], [dateSta]) VALUES (5, N'HTTP://naoqiu.com', CAST(0x0000A03700FF5BF6 AS DateTime))
SET IDE NTITY_INSERT [dbo]. [Orders] OFF
/****** Object:  Default [DF_Orders_dateSta]    ｓｃｒｉｐｔ Date: 04/18/2012 15:31:11 ******/
ALTER TABLE [dbo]. [Orders] ADD  CONSTRAINT [DF_Orders_dateSta]  DEFAULT (getdate()) FOR [dateSta]
GO

       現在我們先看在sql 2005上傳統的分頁預存程序：

Create Procedure [dbo]. [prPageList]
(
    @PageSize int=20,/*每頁行數*/
    @PageIndex int=1,/*傳進頁數*/   & nbsp;
    @Field Nvarchar(2000),/*查詢欄位*/
    @QueryString Nvarchar(3000),/*查詢語句*/
    @Orderby Nvarchar(1000)/*排序欄位*/
)
AS
Set NoCount ON
  Declare @list_id1 int,@ list_id2 int
  DECLARE @SqlQuery Nvarchar(4000)
   Set @list_id2=(@PageSize*@PageIndex)-- 當前頁記錄結束
   Set @list_id1=(@list_id2-@PageSize)--當前頁記錄開始
 
  Set @SqlQuery=N'Select * From (Select ROW_NUMBER() Over(order by '
      +@Orderby
       +N')AS list_id,'
      +@Field
      +' '
 & nbsp;    +@QueryString
      +N')AS A Where A.list_id>'
       +Cast(@list_id1 AS NVarchar)
      +N' and A.list_id<='
       +Cast(@list_id2 AS NVarchar)       
       print @SqlQuery
    Execute sp_executesql @SqlQuery
    
     Set @SqlQuery='Select count(*) '+@QueryString
    Execute sp_executesql @SqlQuery
  & nbsp; RETURN

調用示例：exec prPageList 10,100,'orderID','from orders','dateSta desc'

這種的編寫有以下優點：

公用分頁預存程序代碼精簡，易理解

調用比較簡單，參數少。

缺點：字串超過4000該預存程序執行會發生異常;需要執行兩次檢索表，花費資源大;當選擇第二頁，還需要執行兩次，一次是清單，一次是統計總數。

現在在同等情況下為了提高性能，提高語句執行效率。 需要對以上的分頁預存程序的邏輯思路進行個調整:

在未統計分頁總數情況統計分頁總數，當已經統計過分頁總數，可不需要再次統計分頁總數。

在我們第一位統計分頁總數時，我們需要統計分頁總數，現在我們使用臨時表將符合條件的記錄id插入到臨時表，以方便檢索以及統計。 這樣我們就避免來兩次掃描全表。

在多表關聯篩選資料情況下，我們篩選的條件可能就只在某幾張表，而顯示結果可能需要關聯更多的表情況下，那我們可以將篩選插入臨時表的關聯條件與顯示清單關聯條件作成不一樣。 以准證相容更強。

在我們執行分頁過程中，可能會產生中間臨時表去管理其他的表。

以及完成之後我們可以直接使用到的臨時表刪除釋放資源，而不是等待sql幫我做垃圾回收處理。

更具以上的思路，現在我直接把代碼貼出來：

--綜合公用分頁預存程序
Create procedure [dbo].[ Test_Common_PageList_TemTableByRelation]
@pageIndex int,--當前頁索引，第一頁為：1
@pageSize int,--每頁顯示條數
@count int ,--總數第一次傳0
@id Varchar(40),--主鍵id或關聯欄位
@sortparam Varchar(50),--排序
@selectParam Nvarchar(2000),--查詢欄位
@condition Nvarchar(2000),--查詢準則
@temTable Nvarchar(3000),--臨時表字符串
@dropTable Varchar(300),--刪除臨時表字符串
@relation Varchar(2000),--關聯表
@insrtParam Varchar(100),--插入到統計總量臨時表的欄位
@param Varchar(100)-- 插入到頁顯示的臨時表字段
as
declare @sql Nvarchar(4000),@min int,@max int,@date Varchar(15),@tem1 Varchar(20),@tem2 Varchar (20),@part2 Nvarchar(4000)
set @date=replace(CONVERT(Varchar,getdate(),114),':','')
set @tem1='#page1'+@ date
set @tem2='#page2'+@date
set @min=(@pageIndex-1)*@pageSize+1
set @max=@pageIndex*@pageSize
set @ sql='
select '+@id+' as spkid'+@insrtParam+',ROW_NUMBER() OVER (ORDER BY '+@sortparam+') AS RowNumber into '+@tem1+' f rom '+@condition+'
select spkid,RowNumber'+@param+' into '+@tem2+' from '+@tem1+' where RowNumber between '+CAST(@min as Varchar(11))+' and '+
CAST(@ma x as Varchar(11))
if @count=0 begin
    set @part2='declare @count int
      ;   set @count=(select max(RowNumber) from '+@tem1+')
        if @count is nu ll set @count=0
        select  '+@selectParam+',@count as num'
end else begin
    set @part2='select  '+@selectParam+','+CAST(@count as Varchar(11))+' as num'
end
set @part2=@part2+',RowNumber from '+@tem2+' as pt join '+@relation+' order by pt. RowNumber
truncate table '+@tem1+'
truncate table '+@tem2+'
drop table '+@tem1+'
drop table '+@tem2+'
'+@dropTable
--關閉影響行數
exec('set nocount on
'+@temTable+@sql+@part2+'
set nocount off')
--打開影響行數
GO

調用方法示例:

SET STATISTICS IO on -- 查看磁片IO
set statistics time on -- 查看sql語句分析編譯和執行時間
declare @count int
set @count=0
exec [dbo]. [Erp_Common_PageList_TemTableByRelation] 1,50,@count,'or_id','dateSta desc',
' * ','orders','','','orders on spkid=or_id','',''

總結：在讀取資料過程中盡可能的減少表邏輯讀取次數，以及掃描次數，減少io輸出是提高性能的保證。