求优化该存储过程的方法:通过事件探查器发现其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、求优化方法,先谢谢各位大侠了