熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> .NET編程 >> 正文

SQL大數據量分頁存儲過程效率測試

2013-11-13 09:44:02  來源: .NET編程 

  /*存儲過程 分頁處理 孫偉 創建 */

  /*存儲過程 分頁處理 浪塵 修改*/

  /* 對數據進行了分處理使查詢前半部分數據與查詢後半部分數據性能相同 */

  

  alter PROCEDURE proc_paged_part_selectMax

  (

  @tblName     nvarchar()        要顯示的表或多個表的連接

  @fldName     nvarchar() = *    要顯示的字段列表

  @pageSize    int =         每頁顯示的記錄個數

  @page        int =         要顯示那一頁的記錄

  @fldSort    nvarchar() = null    排序字段列表或條件

  @Sort        bit =         排序方法為升序為降序(如果是多字段排列Sort指代最後一個排序字段的排列順序(最後一個排序字段不加排序標記)程序傳參如 SortA AscSortB DescSortC )

  @strCondition    nvarchar() = null    查詢條件不需where

  @ID        nvarchar()        主表的主鍵

  @Dist                 bit =            是否添加查詢字段的 DISTINCT 默認不添加/添加

  @pageCount    int = output            查詢結果分頁後的總頁數

  @Counts    int = output                查詢到的記錄數

  )

  AS

  SET NOCOUNT ON

  Declare @sqlTmp nvarchar()        存放動態生成的SQL語句

  Declare @strTmp nvarchar()        存放取得查詢結果總數的查詢語句

  Declare @strID     nvarchar()        存放取得查詢開頭或結尾ID的查詢語句

  

  Declare @strSortType nvarchar()    數據排序規則A

  Declare @strFSortType nvarchar()    數據排序規則B

  

  Declare @SqlSelect nvarchar()         對含有DISTINCT的查詢進行SQL構造

  Declare @SqlCounts nvarchar()          對含有DISTINCT的總數查詢進行SQL構造

  

  declare @timediff datetime  耗時測試時間差

  select @timediff=getdate()

  

  if @Dist  =

  begin

      set @SqlSelect = select

      set @SqlCounts = Count(*)

  end

  else

  begin

      set @SqlSelect = select distinct

      set @SqlCounts = Count(DISTINCT +@ID+)

  end

  

  

  if @Sort=

  begin

      set @strFSortType= ASC

      set @strSortType= DESC

  end

  else

  begin

      set @strFSortType= DESC

      set @strSortType= ASC

  end

  

  

  

  生成查詢語句

  此處@strTmp為取得查詢結果數量的語句

  if @strCondition is null or @strCondition=     沒有設置顯示條件

  begin

      set @sqlTmp =  @fldName + From + @tblName

      set @strTmp = @SqlSelect+ @Counts=+@SqlCounts+ FROM +@tblName

      set @strID = From + @tblName

  end

  else

  begin

      set @sqlTmp = + @fldName + From + @tblName + where (>) + @strCondition

      set @strTmp = @SqlSelect+ @Counts=+@SqlCounts+ FROM +@tblName + where (>) + @strCondition

      set @strID = From + @tblName + where (>) + @strCondition

  end

  

  取得查詢結果總數量

  exec sp_executesql @strTmpN@Counts int out @Counts out

  declare @tmpCounts int

  if @Counts =

      set @tmpCounts =

  else

      set @tmpCounts = @Counts

  

      取得分頁總數

      set @pageCount=(@tmpCounts+@pageSize)/@pageSize

  

      /**//**//**//**當前頁大於總頁數 取最後一頁**/

      if @page>@pageCount

          set @page=@pageCount

  

      /*數據分頁分處理*/

      declare @pageIndex int 總數/頁大小

      declare @lastcount int 總數%頁大小

  

      set @pageIndex = @tmpCounts/@pageSize

      set @lastcount = @tmpCounts%@pageSize

      if @lastcount >

          set @pageIndex = @pageIndex +

      else

          set @lastcount = @pagesize

  

      //***顯示分頁

      if @strCondition is null or @strCondition=     沒有設置顯示條件

      begin

          if @pageIndex< or @page<=@pageIndex / + @pageIndex %    前半部分數據處理

              begin

                  if @page=

                      set @strTmp=@SqlSelect+ top + CAST(@pageSize as VARCHAR())+ + @fldName+ from +@tblName

                          + order by + @fldSort + + @strFSortType

                  else

                  begin

                      if @Sort=

                      begin

                      set @strTmp=@SqlSelect+ top + CAST(@pageSize as VARCHAR())+ + @fldName+ from +@tblName

                          + where +@ID+ <(select min(+ @ID +) from (+ @SqlSelect+ top + CAST(@pageSize*(@page) as Varchar()) + + @ID + from +@tblName

                          + order by + @fldSort + + @strFSortType+) AS TBMinID)

                          + order by + @fldSort + + @strFSortType

                      end

                      else

                      begin

                      set @strTmp=@SqlSelect+ top + CAST(@pageSize as VARCHAR())+ + @fldName+ from +@tblName

                          + where +@ID+ >(select max(+ @ID +) from (+ @SqlSelect+ top + CAST(@pageSize*(@page) as Varchar()) + + @ID + from +@tblName

                          + order by + @fldSort + + @strFSortType+) AS TBMinID)

                          + order by + @fldSort + + @strFSortType

                      end

                  end

              end

          else

              begin

              set @page = @pageIndex@page+ 後半部分數據處理

                  if @page <= 最後一頁數據顯示

                      set @strTmp=@SqlSelect+ * from (+@SqlSelect+ top + CAST(@lastcount as VARCHAR())+ + @fldName+ from +@tblName

                          + order by + @fldSort + + @strSortType+) AS TempTB+ order by + @fldSort + + @strFSortType

                  else

                      if @Sort=

                      begin

                      set @strTmp=@SqlSelect+ * from (+@SqlSelect+ top + CAST(@pageSize as VARCHAR())+ + @fldName+ from +@tblName

                          + where +@ID+ >(select max(+ @ID +) from(+ @SqlSelect+ top + CAST(@pageSize*(@page)+@lastcount as Varchar()) + + @ID + from +@tblName

                          + order by + @fldSort + + @strSortType+) AS TBMaxID)

                          + order by + @fldSort + + @strSortType+) AS TempTB+ order by + @fldSort + + @strFSortType

                      end

                      else

                      begin

                      set @strTmp=@SqlSelect+ * from (+@SqlSelect+ top + CAST(@pageSize as VARCHAR())+ + @fldName+ from +@tblName

                          + where +@ID+ <(select min(+ @ID +) from(+ @SqlSelect+ top + CAST(@pageSize*(@page)+@lastcount as Varchar()) + + @ID + from +@tblName

                          + order by + @fldSort + + @strSortType+) AS TBMaxID)

                          + order by + @fldSort + + @strSortType+) AS TempTB+ order by + @fldSort + + @strFSortType

                      end

              end

      end

  

      else 有查詢條件

      begin

          if @pageIndex< or @page<=@pageIndex / + @pageIndex %    前半部分數據處理

          begin

                  if @page=

                      set @strTmp=@SqlSelect+ top + CAST(@pageSize as VARCHAR())+ + @fldName+ from +@tblName

                          + where = + @strCondition + order by + @fldSort + + @strFSortType

                  else if(@Sort=)

                  begin

                      set @strTmp=@SqlSelect+ top + CAST(@pageSize as VARCHAR())+ + @fldName+ from +@tblName

                          + where +@ID+ <(select min(+ @ID +) from (+ @SqlSelect+ top + CAST(@pageSize*(@page) as Varchar()) + + @ID + from +@tblName

                          + where (=) + @strCondition + order by + @fldSort + + @strFSortType+) AS TBMinID)

                          + + @strCondition + order by + @fldSort + + @strFSortType

                  end

                  else

                  begin

                      set @strTmp=@SqlSelect+ top + CAST(@pageSize as VARCHAR())+ + @fldName+ from +@tblName

                          + where +@ID+ >(select max(+ @ID +) from (+ @SqlSelect+ top + CAST(@pageSize*(@page) as Varchar()) + + @ID + from +@tblName

                          + where (=) + @strCondition + order by + @fldSort + + @strFSortType+) AS TBMinID)

                          + + @strCondition + order by + @fldSort + + @strFSortType

                  end

          end

          else

          begin

              set @page = @pageIndex@page+ 後半部分數據處理

              if @page <= 最後一頁數據顯示

                      set @strTmp=@SqlSelect+ * from (+@SqlSelect+ top + CAST(@lastcount as VARCHAR())+ + @fldName+ from +@tblName

                          + where (=) + @strCondition + order by + @fldSort + + @strSortType+) AS TempTB+ order by + @fldSort + + @strFSortType

              else if(@Sort=)

                      set @strTmp=@SqlSelect+ * from (+@SqlSelect+ top + CAST(@pageSize as VARCHAR())+ + @fldName+ from +@tblName

                          + where +@ID+ >(select max(+ @ID +) from(+ @SqlSelect+ top + CAST(@pageSize*(@page)+@lastcount as Varchar()) + + @ID + from +@tblName

                          + where (=) + @strCondition + order by + @fldSort + + @strSortType+) AS TBMaxID)

                          + + @strCondition+ order by + @fldSort + + @strSortType+) AS TempTB+ order by + @fldSort + + @strFSortType

              else

                      set @strTmp=@SqlSelect+ * from (+@SqlSelect+ top + CAST(@pageSize as VARCHAR())+ + @fldName+ from +@tblName

                          + where +@ID+ <(select min(+ @ID +) from(+ @SqlSelect+ top + CAST(@pageSize*(@page)+@lastcount as Varchar()) + + @ID + from +@tblName

                          + where (=) + @strCondition + order by + @fldSort + + @strSortType+) AS TBMaxID)

                          + + @strCondition+ order by + @fldSort + + @strSortType+) AS TempTB+ order by + @fldSort + + @strFSortType

          end

      end

  

  返回查詢結果

  exec sp_executesql @strTmp

  select datediff(ms@timediffgetdate()) as 耗時

  print @strTmp

  SET NOCOUNT OFF

  GO


From:http://tw.wingwit.com/Article/program/net/201311/11443.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.