最近发现现有框架的通用查询存储过程的性能慢,于是仔细研究了下代码:
复制代码 代码如下: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=NULLASBEGIN 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 ENDEND
发现每次查询都需要按条件查询依次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=NULLASBEGIN 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 ENDEND
经过使用临时表的确提高性能,不过有发现一个问题,就是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=NULLASBEGIN 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 ENDEND
您可能感兴趣的文章:
- sqlserver给表添加新字段、给表和字段添加备注、更新备注及查询备注(sql语句)
- 使用SQL语句查询MySQL,SQLServer,Oracle所有数据库名和表名,字段名
- 详解sqlserver查询表索引
- SQLServer中用T—SQL命令查询一个数据库中有哪些表的sql语句
- SQLSERVER查询所有数据库名,表名,和字段名的语句
- mysql和sqlserver查询数据库表的数量方法详解