sqlserver2005利用临时表和@@RowCount提高分页查询存储过程性能示例分享
时间:2024-01-21 05:41:06
最近发现现有框架的通用查询存储过程的性能慢,于是仔细研究了下代码:
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
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
JQuery获取表单值
Python3的urllib.parse常用函数小结(urlencode,quote,quote_plus,unquote,unquote_plus等)
python3将视频流保存为本地视频文件
![](https://img.aspxhome.com/file/2023/1/89281_0s.jpg)
深入了解Django中间件及其方法
![](https://img.aspxhome.com/file/2023/0/70690_0s.jpg)
浅析网页色彩应用
![](https://img.aspxhome.com/file/UploadPic/20101/11/web-color-01-87s.jpg)
Python实现银行账户资金交易管理系统
![](https://img.aspxhome.com/file/2023/1/99671_0s.jpg)
OAuth从1.0到2.1的发展之路
![](https://img.aspxhome.com/file/2023/4/112054_0s.jpg)
vue使用echarts时created里拿到的数据无法渲染的解决
![](https://img.aspxhome.com/file/2023/2/130252_0s.png)
从网页设计开始
python 实现红包随机生成算法的简单实例
Java获取网络文件并插入数据库的代码
python的dataframe转换为多维矩阵的方法
![](https://img.aspxhome.com/file/2023/9/131399_0s.jpg)
Python3实现飞机大战游戏
![](https://img.aspxhome.com/file/2023/6/81826_0s.jpg)
不要忽略了颜色的可用性
![](https://img.aspxhome.com/file/UploadPic/20093/5/01-24s.jpg)
用MySQL内建复制功能来优化可用性
php通过pecl方式安装扩展的实例讲解
nodeJS express路由学习req.body与req.query方法实例详解
![](https://img.aspxhome.com/file/2023/3/122383_0s.jpg)
Dreaweaver MX 2004新功能:图片处理
![](https://img.aspxhome.com/file/UploadPic/20072/200723113029322s.gif)