提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  数据库

当前栏目

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

存储性能 查询 利用 示例 分享 过程 提高
2023-06-13 09:15:16 时间

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

复制代码代码如下:


AlterPROCEDURE[dbo].[AreaSelect]
   @PageSizeint=0,
   @CurrentPageint=1,
   @Identifierint=NULL,
   @ParentIdint=NULL,
   @AreaLevelint=NULL,
   @Childrenint=NULL,
   @AreaNamenvarchar(50)=NULL,
   @Pathnvarchar(MAX)=NULL,
   @Statusint=NULL,
   @Altint=NULL
AS
BEGIN
   SETNOCOUNTON;
   IF(NOT@AreaNameISNULL)   SET@AreaName="%"+@AreaName+"%"
   IF(NOT@PathISNULL)   SET@Path="%"+@Path+"%"
   IF(@PageSize>0)
   BEGIN
       DECLARE@TotalPageint
       Select@TotalPage=Count(Identifier)FROMAreaWhere
       (@IdentifierISNULLorIdentifier=@Identifier)AND
       (@ParentIdISNULLorParentId=@ParentId)AND
       (@AreaLevelISNULLorAreaLevel=@AreaLevel)AND
       (@ChildrenISNULLorChildren=@Children)AND
       (@AreaNameISNULLorAreaNameLike@AreaName)AND
       (@PathISNULLorPathLike@Path)AND
       (@StatusISNULLorStatus=@Status)AND
       (@AltISNULLorAlt=@Alt)
       IF(@TotalPage%@PageSize=0)
       BEGIN
           SET@TotalPage=@TotalPage/@PageSize
       END
       ELSE
       BEGIN
           SET@TotalPage=Round(@TotalPage/@PageSize,0)+1
       END
       SelectTOP(@PageSize)Identifier,ParentId,AreaLevel,Children,AreaName,Path,Status,Alt,@TotalPageastotalPageFROMAreaWhere
       IdentifierNOTIN(SelectTop(@PageSize*(@CurrentPage-1))IdentifierFROMAreaWhere
       (@IdentifierISNULLorIdentifier=@Identifier)AND
       (@ParentIdISNULLorParentId=@ParentId)AND
       (@AreaLevelISNULLorAreaLevel=@AreaLevel)AND
       (@ChildrenISNULLorChildren=@Children)AND
       (@AreaNameISNULLorAreaNameLike@AreaName)AND
       (@PathISNULLorPathLike@Path)AND
       (@StatusISNULLorStatus=@Status)AND
       (@AltISNULLorAlt=@Alt)
           orderbyAreaNameasc)
       AND
       (@IdentifierISNULLorIdentifier=@Identifier)AND
       (@ParentIdISNULLorParentId=@ParentId)AND
       (@AreaLevelISNULLorAreaLevel=@AreaLevel)AND
       (@ChildrenISNULLorChildren=@Children)AND
       (@AreaNameISNULLorAreaNameLike@AreaName)AND
       (@PathISNULLorPathLike@Path)AND
       (@StatusISNULLorStatus=@Status)AND
       (@AltISNULLorAlt=@Alt)
           orderbyAreaNameasc
   END
   ELSE
   BEGIN
       SelectIdentifier,ParentId,AreaLevel,Children,AreaName,Path,Status,AltFROMAreaWhere
       (@IdentifierISNULLorIdentifier=@Identifier)AND
       (@ParentIdISNULLorParentId=@ParentId)AND
       (@AreaLevelISNULLorAreaLevel=@AreaLevel)AND
       (@ChildrenISNULLorChildren=@Children)AND
       (@AreaNameISNULLorAreaNameLike@AreaName)AND
       (@PathISNULLorPathLike@Path)AND
       (@StatusISNULLorStatus=@Status)AND
       (@AltISNULLorAlt=@Alt)
           orderbyAreaNameasc
   END
END

发现每次查询都需要按条件查询依次Area表，性能太低，于是利用临时表将符合条件的记录取出来，然后针对临时表进行查询，代码修改如下：
AlterPROCEDURE[dbo].[AreaSelect]
   @PageSizeint=0,
   @CurrentPageint=1,
   @Identifierint=NULL,
   @ParentIdint=NULL,
   @AreaLevelint=NULL,
   @Childrenint=NULL,
   @AreaNamenvarchar(50)=NULL,
   @Pathnvarchar(MAX)=NULL,
   @Statusint=NULL,
   @Altint=NULL
AS
BEGIN
   SETNOCOUNTON;
   IF(NOT@AreaNameISNULL)   SET@AreaName="%"+@AreaName+"%"
   IF(NOT@PathISNULL)   SET@Path="%"+@Path+"%"

   
   IF(@PageSize>0)
   BEGIN
       --创建临时表
       Select
       Identifier,ParentId,AreaLevel,Children,AreaName,Path,Status,Alt
       INTO#temp_Area
       FROMAreaWhere
       (@IdentifierISNULLorIdentifier=@Identifier)AND
       (@ParentIdISNULLorParentId=@ParentId)AND
       (@AreaLevelISNULLorAreaLevel=@AreaLevel)AND
       (@ChildrenISNULLorChildren=@Children)AND
       (@AreaNameISNULLorAreaNameLike@AreaName)AND
       (@PathISNULLorPathLike@Path)AND
       (@StatusISNULLorStatus=@Status)AND
       (@AltISNULLorAlt=@Alt)
       orderbyAreaNameasc

       DECLARE@TotalPageint
       DECLARE@SumCountint

       --取总数
       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
       SelectTOP(@PageSize)Identifier,ParentId,AreaLevel,Children,AreaName,
       Path,Status,Alt,@TotalPageastotalPage,@SumCountasSumCount
       FROM#temp_Area
       Where
       IdentifierNOTIN(SelectTop(@PageSize*(@CurrentPage-1))IdentifierFROM#temp_Area))
   END
   ELSE
   BEGIN
       SelectIdentifier,ParentId,AreaLevel,Children,AreaName,Path,Status,AltFROMAreaWhere
       (@IdentifierISNULLorIdentifier=@Identifier)AND
       (@ParentIdISNULLorParentId=@ParentId)AND
       (@AreaLevelISNULLorAreaLevel=@AreaLevel)AND
       (@ChildrenISNULLorChildren=@Children)AND
       (@AreaNameISNULLorAreaNameLike@AreaName)AND
       (@PathISNULLorPathLike@Path)AND
       (@StatusISNULLorStatus=@Status)AND
       (@AltISNULLorAlt=@Alt)
           orderbyAreaNameasc
   END
END

经过使用临时表的确提高性能，不过有发现一个问题，就是count(Identifier)的确很耗性能，于是又进行修改了
：

AlterPROCEDURE[dbo].[AreaSelect]
   @PageSizeint=0,
   @CurrentPageint=1,
   @Identifierint=NULL,
   @ParentIdint=NULL,
   @AreaLevelint=NULL,
   @Childrenint=NULL,
   @AreaNamenvarchar(50)=NULL,
   @Pathnvarchar(MAX)=NULL,
   @Statusint=NULL,
   @Altint=NULL
AS
BEGIN
   SETNOCOUNTON;
   IF(NOT@AreaNameISNULL)   SET@AreaName="%"+@AreaName+"%"
   IF(NOT@PathISNULL)   SET@Path="%"+@Path+"%"

   
   IF(@PageSize>0)
   BEGIN
       --创建中记录数
       DECLARE@SumCountint

       --创建临时表
       Select
       Identifier,ParentId,AreaLevel,Children,AreaName,Path,Status,Alt
       INTO#temp_Area
       FROMAreaWhere
       (@IdentifierISNULLorIdentifier=@Identifier)AND
       (@ParentIdISNULLorParentId=@ParentId)AND
       (@AreaLevelISNULLorAreaLevel=@AreaLevel)AND
       (@ChildrenISNULLorChildren=@Children)AND
       (@AreaNameISNULLorAreaNameLike@AreaName)AND
       (@PathISNULLorPathLike@Path)AND
       (@StatusISNULLorStatus=@Status)AND
       (@AltISNULLorAlt=@Alt)
       orderbyAreaNameasc
       --设置总记录数为刚操作的记录数
       SET@SumCount=@@RowCount

       DECLARE@TotalPageint

       IF(@SumCount%@PageSize=0)
       BEGIN
           SET@TotalPage=@SumCount/@PageSize
       END
       ELSE
       BEGIN
           SET@TotalPage=Round(@SumCount/@PageSize,0)+1
       END
       SelectTOP(@PageSize)Identifier,ParentId,AreaLevel,Children,AreaName,
       Path,Status,Alt,@TotalPageastotalPage,@SumCountasSumCount
       FROM#temp_Area
       Where
       IdentifierNOTIN(SelectTop(@PageSize*(@CurrentPage-1))IdentifierFROM#temp_Area))
   END
   ELSE
   BEGIN

       SelectIdentifier,ParentId,AreaLevel,Children,AreaName,Path,Status,AltFROMAreaWhere
       (@IdentifierISNULLorIdentifier=@Identifier)AND
       (@ParentIdISNULLorParentId=@ParentId)AND
       (@AreaLevelISNULLorAreaLevel=@AreaLevel)AND
       (@ChildrenISNULLorChildren=@Children)AND
       (@AreaNameISNULLorAreaNameLike@AreaName)AND
       (@PathISNULLorPathLike@Path)AND
       (@StatusISNULLorStatus=@Status)AND
       (@AltISNULLorAlt=@Alt)
       orderbyAreaNameasc
   END
END

相关文章