求优化该存储过程的方法:通过事件探查器发现其CUP和Reads很高!

xunx 2006-02-22 04:28:39
CREATE proc dbo.DoSave
@UserID int,
@IP varchar(50),
@XS money,
@CPU varchar(100),
@Disk varchar(100),
@ClsID varchar(100)
as
Declare @Sql varchar(50)
Declare @TempID int
Declare @RefUserID int
Set @RefUserID = @UserID
if (@UserID=0)
begin
SELECT @Sql = Convert(varchar(50),RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) ))
set @Sql = RIGHT (@Sql,2)
set @TempID = Convert(int, @Sql)
select @RefUserID = UserID from Sys_User where RefreshOrder=@TempID
end
DECLARE @ClickUserID int
DECLARE @PageID int
DECLARE @ListID int
DECLARE @ClickUrl varchar(100)

Set @PageID = 0
Set @ClickUrl = ''
--初始化参数
select @ListID = ListID,@PageID=PageID,@ClickUserID=UserID,@ClickUrl=Url from Sys_PageList where ListID = (select min( listID) from Sys_PageList)
if (not @ListID is null)
begin
delete Sys_PageList where ListID=@ListID
insert DB2..UserDB2(UserID,XS,ClsID,IP,PageID,PageUserID) values(@RefUserID,@XS,@ClsID,@IP,@PageID,@ClickUserID)
Update Sys_User Set NotOnLineDays=0,LoginDate = getdate(),Sts=1
where UserID = @RefUserID
--9记录内网信息
if ( exists(select 1 from Sys_CheckInnerUser where PCIP = @IP))
update Sys_CheckInnerUser set DiskNo = @Disk , CPUNo = @CPU , CrDate = GetDate()
where PCIP = @IP
else
insert Sys_CheckInnerUser (PCIP , DiskNo ,CPUNo)
values(@IP,@Disk,@CPU)
end
select @ClickUrl as Url
GO
现象:
1、通过事件探查器发现其CUP和Reads很高!
2、单独执行该存储过程需要1分钟
3、Sys_CheckInnerUser 数据小于10000行;Sys_User 小于1000;UserDB2数据在5W左右;Sys_PageList小于1W
4、sqlserver 占系统CUP80%以上,基本都卡在这个存储过程上了
5、求优化方法,先谢谢各位大侠了
...全文
196 11 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
xunx 2006-02-22
  • 打赏
  • 举报
回复
问题已经解决,非常感谢大家得帮助,谢谢!现在结帖
ashzs 2006-02-22
  • 打赏
  • 举报
回复
硬件环境说一下。
ashzs 2006-02-22
  • 打赏
  • 举报
回复
试试建立3个索引,顺序不要错了

--Sys_User非聚集索引(RefreshOrder,UserID)
select @RefUserID = UserID from Sys_User where RefreshOrder=@TempID

--Sys_PageList非聚集索引(listID,PageID,UserID,Url)
select @ListID = ListID,@PageID=PageID,@ClickUserID=UserID,@ClickUrl=Url from Sys_PageList where ListID = (select min( listID) from Sys_PageList)

--Sys_CheckInnerUser非聚集索引(PCIP)
select 1 from Sys_CheckInnerUser where PCIP = @IP
ashzs 2006-02-22
  • 打赏
  • 举报
回复
二楼:
DB2..UserDB2不是联接服务器吧?

-----------------------------------

不好意思,看错了。
Yang_ 2006-02-22
  • 打赏
  • 举报
回复
加索引:
Sys_CheckInnerUser(PCIP )
Sys_User ( UserID )
Sys_PageList (listID)
xunx 2006-02-22
  • 打赏
  • 举报
回复
使用以上两位大侠的方法后有所改善(Sys_PageList上的锁已经消逝),先谢谢了。但是通过事件探查器发现该存储过程的CUP和Reads还是很高(平均:CUP 35 Reads:450),请问还有没有办法使其占用的CPU下降一些(已经打过SP4,OS:Win2003)!
xunx 2006-02-22
  • 打赏
  • 举报
回复
每分钟调用频率在1000次左右
xunx 2006-02-22
  • 打赏
  • 举报
回复
说明一下:这个存储过程是用了记录新闻被浏览信息的,原来UserDB2是在一个库中,由于性能太慢才单独到其他库的,该存储过程调用特别频繁,就想知道是否是因为相关表出了主键外没有其他索引引起的,还是有什么方法优化一下。单独执行select 1 from Sys_CheckInnerUser where PCIP = @IP需要40多秒
Yang_ 2006-02-22
  • 打赏
  • 举报
回复
语句优化空间不大,考虑是否索引问题

建议改动:
1、在as 后加 set nocount on 一句
2、
select @ListID = ListID,@PageID=PageID,@ClickUserID=UserID,@ClickUrl=Url from Sys_PageList where ListID = (select min( listID) from Sys_PageList)
改成
select top 1 @ListID = ListID,@PageID=PageID,@ClickUserID=UserID,@ClickUrl=Url from Sys_PageList order by listID
3、Sys_PageList 表如果数据多的话看看listID列有没索引,最后是聚集索引
4、考虑下是否需要用事务


二楼:
DB2..UserDB2不是联接服务器吧?


ashzs 2006-02-22
  • 打赏
  • 举报
回复
你的这个存储过程从整体来说没什么问题。但是请说明以下几点:
1、这个存储过程是不是频繁调度?如果是,调用频率多少?

如果频繁调用,由于有
insert DB2..UserDB2(UserID,XS,ClsID,IP,PageID,PageUserID) values(@RefUserID,@XS,@ClsID,@IP,@PageID,@ClickUserID)
这个链接服务在,很可能导致第一个存储过程没有执行完毕,第二个又开始了。

如果是这样,请考虑能否将insert到DB2上的东西,先缓存在sql server上,等到达一定的数据量后批量提交到db2上。

2、如果上面不能解决,请说明你使用的事务隔离级别。默认为read committed。
通过这个可以判断一下是否隔离级别过高导致诸塞。





xunx 2006-02-22
  • 打赏
  • 举报
回复
在Sys_PageList上发现有以下类型的锁:TAB、PAG、RID,查看内容都是这个存储过程引起的

22,301

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧