T-SQL TryCatch性能粗略測試

来源：互联网发布：数据库认证编辑：程序博客网时间：2024/05/16 00:45

预先建立测试Table，并初始化数据：

CREATE TABLE [dbo].[PDCA_FLATFILE_QUEUE]([FileID] [int] IDENTITY(1,1) NOT NULL,[TransID] [int] NOT NULL,[CDT] [datetime] NOT NULL CONSTRAINT [DF_PDCA_FLATFILE_QUEUE_CDT]  DEFAULT (getdate()),[UDT] [datetime] NOT NULL CONSTRAINT [DF_PDCA_FLATFILE_QUEUE_UDT]  DEFAULT (getdate()), CONSTRAINT [PK_PDCA_FLATFILE_QUEUE] PRIMARY KEY CLUSTERED([FileID] ASC))GOCREATE TABLE [dbo].[PDCA_FLATFILE_QUEUE_LOG]([FileID] [int] NOT NULL,[TransID] [int] NOT NULL,[FileName] [varchar](500) NOT NULL,[QueueCDT] [datetime] NOT NULL,[QueueUDT] [datetime] NOT NULL,[LogCdt] [datetime] NOT NULL)GOCREATE UNIQUE NONCLUSTERED INDEX [IX_PDCA_FLATFILE_QUEUE_LOG] ON [dbo].[PDCA_FLATFILE_QUEUE_LOG]([FileID] ASC)GOdeclare @i intselect @i=1while @i<100000begininsert PDCA_FLATFILE_QUEUE(TransID) values (@i)select @i=@i+1end

再看待测试的语句，将其封装成存储过程，使用了交易(transaction), 并使用GOTO语句实现错误时跳转：

CREATE PROCEDURE [dbo].[uspPDCAFlatFile_SavePostLog](@FileID int,@FileName varchar(500),@ResponseResult tinyint, --0:Seccuss; 1:NoResponse or TransferFail; 2:FileError@ResponseStatus tinyint, --200/non-200@ResponseMessage varchar(100)='' --0:need requeue; 1:Seccuss; 2/3/4:requeue after fix error)ASdeclare @ErrorFlag intselect @ErrorFlag=0declare @c intbegin traninsert PDCA_FLATFILE_QUEUE_LOG select FileID,TransID,@FileName,CDT,UDT,GETDATE() from PDCA_FLATFILE_QUEUE where FileID=@FileIDif @@ERROR<>0 begin select @ErrorFlag=1 GOTO EndFlag end--delete PDCA_FLATFILE_QUEUE where FileID=@FileID--if @@ERROR<>0 begin select @ErrorFlag=1 GOTO EndFlag endselect @c=10000while @c>0beginselect @c=@c-1endif @@ERROR<>0 begin select @ErrorFlag=1 GOTO EndFlag endEndFlag:IF @@ERROR<>0 or @ErrorFlag=1beginrollback transelect RetCode=1,FileID=@FileID,Msg='PDCAFlatFile_SavePostLog Transaction Error!'endelsebegincommit transelect RetCode=0,FileID=@FileID,Msg='PDCAFlatFile_SavePostLog seccuss!'end

使用TRY Catch修改其中部分代码：

begin tranbegin tryinsert PDCA_FLATFILE_QUEUE_LOG select FileID,TransID,@FileName,CDT,UDT,GETDATE() from PDCA_FLATFILE_QUEUE where FileID=@FileID--delete PDCA_FLATFILE_QUEUE where FileID=@FileIDselect @c=10000while @c>0beginselect @c=@c-1endcommit transelect RetCode=0,FileID=@FileID,Msg='PDCAFlatFile_SavePostLog seccuss!'end trybegin catchrollback transelect RetCode=1,FileID=@FileID,Msg='PDCAFlatFile_SavePostLog Transaction Error!'end catch

最后用下面的语句进行测试：

delete PDCA_FLATFILE_QUEUE_LOGdeclare @timeStamp datetimeselect @timeStamp=GETDATE()declare @minFileId intdeclare @tmpFileId intdeclare @ExecSeqid intdeclare @Total intdeclare @DupliRunWhileXTimes intselect @minFileId=min(fileid) from PDCA_FLATFILE_QUEUEselect @ExecSeqid=0,@Total=100,@DupliRunWhileXTimes=5while @ExecSeqid<@Totalbeginselect @tmpFileId=@minFileId+@ExecSeqidif @ExecSeqid%@DupliRunWhileXTimes=0beginexec uspPDCAFlatFile_SavePostLog_TryCatch @tmpFileId,'CSMC_IFATP_YLD_K93_20120309073355.zip',0,0exec uspPDCAFlatFile_SavePostLog_TryCatch @tmpFileId,'CSMC_IFATP_YLD_K93_20120309073355.zip',0,0endelseexec uspPDCAFlatFile_SavePostLog_TryCatch @tmpFileId,'CSMC_IFATP_YLD_K93_20120309073355.zip',0,0select @ExecSeqid=@ExecSeqid+1endprint datediff(ms,@timeStamp,GETDATE())

测试结果：用两种写法测试上面代码的执行时间，使用GOTO及@ErrorFlag判断的方式，执行了5200ms左右；使用Try Catch方式，执行耗时4500ms左右。可见， Try Catch方式并未多了系统开销而变慢，反而因减少了部分判断语句而提高了执行速度。

结论：可以使用Try Catch代替“GOTO及@ErrorFlag判断”，执行效能不会变差，而代码会变得更为简洁，易于维护。

0 0