主页 > 知识库 > sqlserver2005利用临时表和@@RowCount提高分页查询存储过程性能示例分享

sqlserver2005利用临时表和@@RowCount提高分页查询存储过程性能示例分享

热门标签:海南自动外呼系统价格 松原导航地图标注 九鹿林外呼系统怎么收费 沧州营销外呼系统软件 沈阳智能外呼系统代理 创业电销机器人 浙江地图标注 电销机器人虚拟号码 舞钢市地图标注app

最近发现现有框架的通用查询存储过程的性能慢,于是仔细研究了下代码:

复制代码 代码如下:

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

您可能感兴趣的文章:
  • sql server2008调试存储过程的完整步骤
  • SQLServer2008存储过程实现数据插入与更新
  • Sql Server 存储过程调用存储过程接收输出参数返回值
  • SQLServer存储过程创建和修改的实现代码
  • 获取SqlServer存储过程定义的三种方法
  • SqlServer存储过程实现及拼接sql的注意点
  • 浅析SQL Server的嵌套存储过程中使用同名的临时表怪像

标签:宝鸡 咸宁 商洛 海口 台湾 西藏 日喀则 公主岭

巨人网络通讯声明:本文标题《sqlserver2005利用临时表和@@RowCount提高分页查询存储过程性能示例分享》,本文关键词  sqlserver2005,利用,临时,表,;如发现本文内容存在版权问题,烦请提供相关信息告之我们,我们将及时沟通与处理。本站内容系统采集于网络,涉及言论、版权与本站无关。
  • 相关文章
  • 下面列出与本文章《sqlserver2005利用临时表和@@RowCount提高分页查询存储过程性能示例分享》相关的同类信息!
  • 本页收集关于sqlserver2005利用临时表和@@RowCount提高分页查询存储过程性能示例分享的相关信息资讯供网民参考!
  • 企业400电话

    智能AI客服机器人
    15000

    在线订购

    合计11份范本:公司章程+合伙协议+出资协议+合作协议+股权转让协议+增资扩股协议+股权激励+股东会决议+董事会决议

    推荐文章