程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> 更多數據庫知識 >> sqlserver2005利用臨時表和@@RowCount提高分頁查詢存儲過程性能示例分享

sqlserver2005利用臨時表和@@RowCount提高分頁查詢存儲過程性能示例分享

編輯:更多數據庫知識

最近發現現有框架的通用查詢存儲過程的性能慢,於是仔細研究了下代碼:

復制代碼 代碼如下:
Alter PROCEDURE [dbo].[AreaSelect]
    @PageSize int=0,
    @CurrentPage int=1,
    @Identifier int=NULL,
    @ParentId int=NULL,
    @AreaLevel int=NULL,
    @Children int=NULL,
    @AreaName nvarchar(50)=NULL,
    @Path nvarchar(MAX)=NULL,
    @Status int=NULL,
    @Alt int=NULL
AS
BEGIN
    SET NOCOUNT ON;
    IF (NOT @AreaName IS NULL)    SET @AreaName='%'+@AreaName+'%'
    IF (NOT @Path IS NULL)    SET @Path='%'+@Path+'%'
    IF (@PageSize>0)
    BEGIN
        DECLARE @TotalPage int
        Select @TotalPage=Count(Identifier) FROM Area Where
        (@Identifier IS NULL or Identifier=@Identifier)AND
        (@ParentId IS NULL or ParentId=@ParentId)AND
        (@AreaLevel IS NULL or AreaLevel=@AreaLevel)AND
        (@Children IS NULL or Children=@Children)AND
        (@AreaName IS NULL or AreaName Like @AreaName)AND
        (@Path IS NULL or Path Like @Path)AND
        (@Status IS NULL or Status=@Status)AND
        (@Alt IS NULL or Alt=@Alt)
        IF(@TotalPage%@PageSize=0)
        BEGIN
            SET @TotalPage=@TotalPage/@PageSize
        END
        ELSE
        BEGIN
            SET @TotalPage=Round(@TotalPage/@PageSize,0)+1
        END
        Select TOP (@PageSize) Identifier,ParentId,AreaLevel,Children,AreaName,Path,Status,Alt,@TotalPage as totalPage FROM Area Where
        Identifier NOT IN (Select Top (@PageSize*(@CurrentPage-1))Identifier FROM Area Where
        (@Identifier IS NULL or Identifier=@Identifier)AND
        (@ParentId IS NULL or ParentId=@ParentId)AND
        (@AreaLevel IS NULL or AreaLevel=@AreaLevel)AND
        (@Children IS NULL or Children=@Children)AND
        (@AreaName IS NULL or AreaName Like @AreaName)AND
        (@Path IS NULL or Path Like @Path)AND
        (@Status IS NULL or Status=@Status)AND
        (@Alt IS NULL or Alt=@Alt)
            order by AreaName asc)
        AND
        (@Identifier IS NULL or Identifier=@Identifier)AND
        (@ParentId IS NULL or ParentId=@ParentId)AND
        (@AreaLevel IS NULL or AreaLevel=@AreaLevel)AND
        (@Children IS NULL or Children=@Children)AND
        (@AreaName IS NULL or AreaName Like @AreaName)AND
        (@Path IS NULL or Path Like @Path)AND
        (@Status IS NULL or Status=@Status)AND
        (@Alt IS NULL or Alt=@Alt)
            order by AreaName asc
    END
    ELSE
    BEGIN
        Select Identifier,ParentId,AreaLevel,Children,AreaName,Path,Status,Alt FROM Area Where
        (@Identifier IS NULL or Identifier=@Identifier)AND
        (@ParentId IS NULL or ParentId=@ParentId)AND
        (@AreaLevel IS NULL or AreaLevel=@AreaLevel)AND
        (@Children IS NULL or Children=@Children)AND
        (@AreaName IS NULL or AreaName Like @AreaName)AND
        (@Path IS NULL or Path Like @Path)AND
        (@Status IS NULL or Status=@Status)AND
        (@Alt IS NULL or Alt=@Alt)
            order by AreaName asc
    END
END

發現每次查詢都需要按條件查詢依次Area表,性能太低,於是利用臨時表將符合條件的記錄取出來,然後針對臨時表進行查詢,代碼修改如下:
Alter PROCEDURE [dbo].[AreaSelect]
    @PageSize int=0,
    @CurrentPage int=1,
    @Identifier int=NULL,
    @ParentId int=NULL,
    @AreaLevel int=NULL,
    @Children int=NULL,
    @AreaName nvarchar(50)=NULL,
    @Path nvarchar(MAX)=NULL,
    @Status int=NULL,
    @Alt int=NULL
AS
BEGIN
    SET NOCOUNT ON;
    IF (NOT @AreaName IS NULL)    SET @AreaName='%'+@AreaName+'%'
    IF (NOT @Path IS NULL)    SET @Path='%'+@Path+'%'

   
    IF (@PageSize>0)
    BEGIN
        --創建臨時表
        Select
        Identifier,ParentId,AreaLevel,Children,AreaName,Path,Status,Alt
        INTO #temp_Area
        FROM Area Where
        (@Identifier IS NULL or Identifier=@Identifier)AND
        (@ParentId IS NULL or ParentId=@ParentId)AND
        (@AreaLevel IS NULL or AreaLevel=@AreaLevel)AND
        (@Children IS NULL or Children=@Children)AND
        (@AreaName IS NULL or AreaName Like @AreaName)AND
        (@Path IS NULL or Path Like @Path)AND
        (@Status IS NULL or Status=@Status)AND
        (@Alt IS NULL or Alt=@Alt)
        order by AreaName asc

        DECLARE @TotalPage int
        DECLARE @SumCount int

        --取總數
        Select @SumCount=Count(Identifier) FROM #temp_Area

        IF(@SumCount%@PageSize=0)
        BEGIN
            SET @TotalPage=@SumCount/@PageSize
        END
        ELSE
        BEGIN
            SET @TotalPage=Round(@SumCount/@PageSize,0)+1
        END
        Select TOP (@PageSize) Identifier,ParentId,AreaLevel,Children,AreaName,
        Path,Status,Alt,@TotalPage as totalPage,@SumCount as SumCount
        FROM #temp_Area
        Where
        Identifier NOT IN (Select Top (@PageSize*(@CurrentPage-1))Identifier FROM #temp_Area))
    END
    ELSE
    BEGIN
        Select Identifier,ParentId,AreaLevel,Children,AreaName,Path,Status,Alt FROM Area Where
        (@Identifier IS NULL or Identifier=@Identifier)AND
        (@ParentId IS NULL or ParentId=@ParentId)AND
        (@AreaLevel IS NULL or AreaLevel=@AreaLevel)AND
        (@Children IS NULL or Children=@Children)AND
        (@AreaName IS NULL or AreaName Like @AreaName)AND
        (@Path IS NULL or Path Like @Path)AND
        (@Status IS NULL or Status=@Status)AND
        (@Alt IS NULL or Alt=@Alt)
            order by AreaName asc
    END
END

經過使用臨時表的確提高性能,不過有發現一個問題,就是count(Identifier)的確很耗性能,於是又進行修改了

Alter PROCEDURE [dbo].[AreaSelect]
    @PageSize int=0,
    @CurrentPage int=1,
    @Identifier int=NULL,
    @ParentId int=NULL,
    @AreaLevel int=NULL,
    @Children int=NULL,
    @AreaName nvarchar(50)=NULL,
    @Path nvarchar(MAX)=NULL,
    @Status int=NULL,
    @Alt int=NULL
AS
BEGIN
    SET NOCOUNT ON;
    IF (NOT @AreaName IS NULL)    SET @AreaName='%'+@AreaName+'%'
    IF (NOT @Path IS NULL)    SET @Path='%'+@Path+'%'

   
    IF (@PageSize>0)
    BEGIN
        --創建中記錄數
        DECLARE @SumCount int

        --創建臨時表
        Select
        Identifier,ParentId,AreaLevel,Children,AreaName,Path,Status,Alt
        INTO #temp_Area
        FROM Area Where
        (@Identifier IS NULL or Identifier=@Identifier)AND
        (@ParentId IS NULL or ParentId=@ParentId)AND
        (@AreaLevel IS NULL or AreaLevel=@AreaLevel)AND
        (@Children IS NULL or Children=@Children)AND
        (@AreaName IS NULL or AreaName Like @AreaName)AND
        (@Path IS NULL or Path Like @Path)AND
        (@Status IS NULL or Status=@Status)AND
        (@Alt IS NULL or Alt=@Alt)
        order by AreaName asc
        --設置總記錄數為剛操作的記錄數
        SET @SumCount=@@RowCount

        DECLARE @TotalPage int

        IF(@SumCount%@PageSize=0)
        BEGIN
            SET @TotalPage=@SumCount/@PageSize
        END
        ELSE
        BEGIN
            SET @TotalPage=Round(@SumCount/@PageSize,0)+1
        END
        Select TOP (@PageSize) Identifier,ParentId,AreaLevel,Children,AreaName,
        Path,Status,Alt,@TotalPage as totalPage,@SumCount as SumCount
        FROM #temp_Area
        Where
        Identifier NOT IN (Select Top (@PageSize*(@CurrentPage-1))Identifier FROM #temp_Area))
    END
    ELSE
    BEGIN

        Select Identifier,ParentId,AreaLevel,Children,AreaName,Path,Status,Alt FROM Area Where
        (@Identifier IS NULL or Identifier=@Identifier)AND
        (@ParentId IS NULL or ParentId=@ParentId)AND
        (@AreaLevel IS NULL or AreaLevel=@AreaLevel)AND
        (@Children IS NULL or Children=@Children)AND
        (@AreaName IS NULL or AreaName Like @AreaName)AND
        (@Path IS NULL or Path Like @Path)AND
        (@Status IS NULL or Status=@Status)AND
        (@Alt IS NULL or Alt=@Alt)
        order by AreaName asc
    END
END

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