優化SQL2005

這兩天測試了前幾天寫的SQL2005專用分頁的存儲過程，當資料量達到2千多萬的時候，效率相當的低，每次執行都要8秒左右（CPU：Q6600）。不過在2百多萬資料量的情況下性能還是蠻不錯的，在網上找了找，發現這下面的這兩個，其實還是一個，不過後面那個是靈活了許多，僅供參考。 

-SQL

SET ANSI_NULLS  ON

GO

SET QUOTED_IDENTIFIER  ON

GO

ALTER  PROCEDURE [dbo].[GetRecordFromPage]

    @tblName       varchar(255),        -- 表名

    @fldName       varchar(255),        -- 字段名

    @PageSize      int = 10,            -- 頁尺寸

    @PageIndex     int = 1,             -- 頁碼

    @OrderType     bit = 0,             -- 設定排序類型, 非 0 值則降序

    @strWhere      varchar(2000) =  ''   -- 查詢條件 (注意: 不要加 where)

AS

declare @strSQL    varchar(6000)        -- 主語句

declare @strTmp    varchar(1000)        -- 臨時變量

declare @strOrder  varchar(500)         -- 排序類型

if @OrderType != 0

begin

     set @strTmp =  '<(select min'

     set @strOrder =  ' order by [' + @fldName + '] desc'

end

else

begin

     set @strTmp =  '>(select max'

     set @strOrder =  ' order by [' + @fldName + '] asc'

end

set @strSQL =  'select top ' + str(@PageSize) +  ' * from ['

    + @tblName +  '] where [' + @fldName +  ']' + @strTmp +  '(['

    + @fldName +  ']) from (select top ' + str((@PageIndex-1)*@PageSize) +  ' ['

    + @fldName +  '] from [' + @tblName +  ']' + @strOrder +  ') as tblTmp)'

    + @strOrder

if @strWhere !=  ''

     set @strSQL =  'select top ' + str(@PageSize) +  ' * from ['

        + @tblName +  '] where [' + @fldName +  ']' + @strTmp +  '(['

        + @fldName +  ']) from (select top ' + str((@PageIndex-1)*@PageSize) +  ' ['

        + @fldName +  '] from [' + @tblName +  '] where ' + @strWhere +  ' '

        + @strOrder +  ') as tblTmp) and ' + @strWhere +  ' ' + @strOrder

if @PageIndex = 1

begin

     set @strTmp =  ''

     if @strWhere !=  ''

         set @strTmp =  ' where (' + @strWhere +  ')'

     set @strSQL =  'select top ' + str(@PageSize) +  ' * from ['

        + @tblName +  ']' + @strTmp +  ' ' + @strOrder

end

exec (@strSQL)

這是我目前見過效率最高的，不過它的order by 是不是有點兒問題，不能指定某一個，還有沒有傳回總記錄數。于是有了下面這個改進的。 

如果需要總頁數還可以再改一下，不過目前已經足夠用了，當然，當達到千萬資料量後，比上面的效率上稍微低了一點點，不過還不算太壞， 

比較推薦下面這個。 

-VBScript

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE       PROCEDURE [dbo].[usp_GetRecordFromPage]

    @tblName       varchar(1000),        -- 表名

    @SelectFieldName    varchar(4000),              -- 要顯示的字段名(不要加select)

    @strWhere       varchar(4000),              -- 查詢條件(注意: 不要加 where)

    @OrderFieldName      varchar(255),               -- 排序索引字段名

    @PageSize        int ,                 -- 頁大小

    @PageIndex       int = 1,                  -- 頁碼

    @iRowCount       int output,                 -- 傳回記錄總數

    @OrderType      bit = 0                  -- 設定排序類型, 非 0 值則降序

AS

declare @strSQL    varchar(4000)       -- 主語句

declare @strTmp    varchar(4000)        -- 臨時變量

declare @strOrder  varchar(400)        -- 排序類型

declare @strRowCount    nvarchar(4000)      -- 用于查詢記錄總數的語句

set @OrderFieldName= ltrim( rtrim(@OrderFieldName))

if @OrderType != 0

begin

     set @strTmp =  '<(select min'

     set @strOrder =  ' order by ' + @OrderFieldName +' desc'

end

else

begin

     set @strTmp =  '>(select max'

     set @strOrder =  ' order by ' + @OrderFieldName +' asc'

end

set @strSQL =  'select top ' + str(@PageSize) + @SelectFieldName+' from '

    + @tblName +  ' where ' + @OrderFieldName + @strTmp + '('

    +  right(@OrderFieldName, len(@OrderFieldName)-charindex( '.',@OrderFieldName)) + ') from (select top ' + str((@PageIndex-1)*@PageSize)

    + @OrderFieldName +  ' from ' + @tblName  + @strOrder + ') as tblTmp)'

    + @strOrder

if @strWhere !=  ''

     set @strSQL =  'select top ' + str(@PageSize) + @SelectFieldName+' from '

        + @tblName +  ' where ' + @OrderFieldName + @strTmp + '('

        +  right(@OrderFieldName, len(@OrderFieldName)-charindex( '.',@OrderFieldName)) + ') from (select top ' + str((@PageIndex-1)*@PageSize)

        + @OrderFieldName +  ' from ' + @tblName + ' where ' + @strWhere + ' '

        + @strOrder +  ') as tblTmp) and ' + @strWhere + ' ' + @strOrder

if @PageIndex = 1

begin

     set @strTmp =  ''

     if @strWhere !=  ''

         set @strTmp =  ' where ' + @strWhere

     set @strSQL =  'select top ' + str(@PageSize) + @SelectFieldName+' from '

        + @tblName + @strTmp +  ' ' + @strOrder

end

exec(@strSQL)

if @strWhere!= ''

begin

   set @strRowCount =  'select @iRowCount=count(*) from ' + @tblName+' where '[email protected]

end

else

begin

   set @strRowCount =  'select @iRowCount=count(*) from ' + @tblName

end

exec sp_executesql @strRowCount,N '@iRowCount int out',@iRowCount out