如何加快存储过程的执行速度?

truesmx 2005-02-15 10:47:06

请大家帮看一下下面的存储过程，如何加快这个程序的执行速度
一般情况下，得需要1分钟,上次在服务器上执行用了40分，晕呀！幸亏服务器不忙，请大家帮看，如何加快程序的执行速度！
谢谢！

--表A中有10万多条记录
DECLARE CURSOR1 CURSOR FAST_FORWARD
FOR SELECT [ID],CarNumber,[DateTime],CarAddress,CarType,CarWhys,DisposalTime FROM a
WHERE DisposalType='已处理' AND Operation='0'
OPEN CURSOR1
IF @@ERROR=0/* Open cursor success */
BEGIN
DECLARE @Car_Number varchar(20),@DateTime datetime,@CarAddress varchar(50),
@CarType varchar(10),@CarWhys varchar(50),@DisposalTime datetime,
@DisposalType varchar(80),@Msg varchar(150),@Msg2 varchar(150),@Msg3 varchar(150),
@strChild varchar(150),@strTemp varchar(50),@ID int,@ID2 int,@IntCount int

FETCH NEXT FROM CURSOR1 INTO @ID,@Car_Number,@DateTime,@CarAddress,@CarType,@CarWhys,@DisposalTime

SET @IntCount=0

WHILE @@FETCH_STATUS = 0/* FETCH SUCCESS */
BEGIN
SET @strChild=@Car_Number+'朋友'
SET @Msg=@strChild+'春节快乐!'
SET @Msg2=@strChild+'新年快乐!'
SET @Msg3=@strChild+'您好!'

--------------求得日期
SET @strChild=''
SET @strTemp=''

SET @strTemp=RIGHT(STR(YEAR(@DateTime),4,2),2)+'年'+LTRIM(STR(MONTH(@DateTime),2,0))+'月

'+LTRIM(STR(DAY(@DateTime),2,0))+'日'

IF DATENAME(Hour,@DateTime)<>'0'
BEGIN
SET @strTemp=@strTemp+DATENAME(Hour,@DateTime)+':'
IF LEN(DATENAME(minute, @DateTime))<2
SET @strTemp=@strTemp+'0'
SET @strTemp=@strTemp+DATENAME(minute,@DateTime)
END
ELSE--可能是00:00 或00:06
BEGIN
IF DATENAME(minute, @DateTime)<>'0'
BEGIN
SET @strTemp=@strTemp+'0:'
IF LEN(DATENAME(minute, @DateTime))<2
SET @strTemp=@strTemp+'0'
SET @strTemp=@strTemp+DATENAME(minute,@DateTime)
END
END

--PRINT @strTemp
IF @strTemp<>''
SET @strChild='于'+@strTemp+'违章.'

--------------求得日期

IF @CarType<>''--CarType
SET @strChild=@strChild+@CarType+'.'

SET @strTemp=''
IF @CarAddress<>''--CarAddress
SET @strTemp='地点:'+@CarAddress

SET @CarWhys=REPLACE(@CarWhys,'，',',')
IF @CarWhys<>''--CarWhys
BEGIN
IF @strTemp<>''
SET @strTemp=@strTemp+','+@CarWhys
ELSE
SET @strTemp=@CarWhys
END

IF @strTemp<>''
SET @strTemp=@strTemp+'.'

SET @strChild=@strChild+@strTemp

SET @strChild=@strChild+RIGHT(STR(YEAR(@DisposalTime),4,2),2)+'年'+LTRIM(STR(MONTH(@DisposalTime),2,0))+
'月'+LTRIM(STR(DAY(@DisposalTime),2,0))+'日已处理.'

SET @Msg=@Msg+@strChild
SET @Msg2=@Msg2+@strChild
SET @Msg3=@Msg3+@strChild

--表DriverSubmitConvert中数据不多，最多1000条记录,字段Msg_Content是字符串类型
DECLARE CURSOR2 CURSOR FAST_FORWARD
FOR SELECT [ID] FROM DriverSubmitConvert WHERE ((Msg_Content like '%'+@Msg+'%')
OR (Msg_Content like '%'+@Msg2+'%') OR (Msg_Content like '%'+@Msg3+'%'))
OPEN CURSOR2
IF @@ERROR=0/* Open cursor success */
BEGIN

FETCH NEXT FROM CURSOR2 INTO @ID2
IF @@FETCH_STATUS = 0
BEGIN
SET @IntCount=@IntCount+1
UPDATE a SET Operation='1' WHERE [ID]=@ID
PRINT @IntCount
PRINT @ID2
PRINT @Msg
--PRINT @Msg2
--PRINT @Msg3
END
END
ELSE
PRINT 'ERROR2!'

CLOSE CURSOR2
DEALLOCATE CURSOR2

--BREAK
FETCH NEXT FROM CURSOR1 INTO @ID,@Car_Number,@DateTime,@CarAddress,@CarType,@CarWhys,@DisposalTime
END
END
ELSE/* Open cursor failed */
PRINT 'ERROR!'

CLOSE CURSOR1
DEALLOCATE CURSOR1
GO

...全文