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

發(fā)現(xiàn)每次查詢都需要按條件查詢依次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
        --創(chuàng)建臨時表
        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

        --取總數(shù)
        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

經(jīng)過使用臨時表的確提高性能，不過有發(fā)現(xiàn)一個問題，就是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
        --創(chuàng)建中記錄數(shù)
        DECLARE @SumCount int

        --創(chuàng)建臨時表
        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
        --設(shè)置總記錄數(shù)為剛操作的記錄數(shù)
        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