该存储过程的代码如下,/*-------------------------修改记录-------------------------------
**
** 修改人:
-----------------------------------------------------------------*/CREATE PROCEDURE dbo.ODRECORD_Main_Reserve(
@type nvarchar(2),
@Loginer NVARCHAR(6),
@GLIDEID NVARCHAR(20) --订单号
)
AS
BEGIN
DECLARE @sql NVARCHAR(255)
DECLARE @WSID NVARCHAR(20)
DECLARE @GDID NVARCHAR(20)
DECLARE @GDSUM NVARCHAR(20) SET @sql = 'DELETE FROM ODRECORD_Main WHERE GLIDEID = ''' + @GLIDEID + '''' DECLARE my_cursor CURSOR FOR SELECT X1.WSID, X2.GDID, X2.GDSUM FROM ODRECORD_Main X1 RIGHT
JOIN ODRECORD X2 ON X1.GLIDEID = X2.ODID WHERE X1.GLIDEID = @GLIDEID
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @WSID, @GDID, @GDSUM WHILE @@FETCH_STATUS = 0
BEGIN
EXEC GdStock_Operation @type,@WSID,@GDID,@GDSUM
FETCH NEXT FROM my_cursor INTO @WSID, @GDID, @GDSUM
END CLOSE my_cursor
deallocate my_cursor
EXEC (@sql)
IF @@error<>0
RETURN 0
else
BEGIN
--EXEC Add_LOG @Loginer, '03', @SQL, ''
SET @sql = 'DELETE FROM ODRECORD WHERE ODID = ''' + @GLIDEID + ''''
exec (@sql)
--EXEC Add_LOG @Loginer, '03', @sql, ''
IF @@ERROR = 0
RETURN 1
ELSE
RETURN 0
END
ENDGO谢谢!
**
** 修改人:
-----------------------------------------------------------------*/CREATE PROCEDURE dbo.ODRECORD_Main_Reserve(
@type nvarchar(2),
@Loginer NVARCHAR(6),
@GLIDEID NVARCHAR(20) --订单号
)
AS
BEGIN
DECLARE @sql NVARCHAR(255)
DECLARE @WSID NVARCHAR(20)
DECLARE @GDID NVARCHAR(20)
DECLARE @GDSUM NVARCHAR(20) SET @sql = 'DELETE FROM ODRECORD_Main WHERE GLIDEID = ''' + @GLIDEID + '''' DECLARE my_cursor CURSOR FOR SELECT X1.WSID, X2.GDID, X2.GDSUM FROM ODRECORD_Main X1 RIGHT
JOIN ODRECORD X2 ON X1.GLIDEID = X2.ODID WHERE X1.GLIDEID = @GLIDEID
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @WSID, @GDID, @GDSUM WHILE @@FETCH_STATUS = 0
BEGIN
EXEC GdStock_Operation @type,@WSID,@GDID,@GDSUM
FETCH NEXT FROM my_cursor INTO @WSID, @GDID, @GDSUM
END CLOSE my_cursor
deallocate my_cursor
EXEC (@sql)
IF @@error<>0
RETURN 0
else
BEGIN
--EXEC Add_LOG @Loginer, '03', @SQL, ''
SET @sql = 'DELETE FROM ODRECORD WHERE ODID = ''' + @GLIDEID + ''''
exec (@sql)
--EXEC Add_LOG @Loginer, '03', @sql, ''
IF @@ERROR = 0
RETURN 1
ELSE
RETURN 0
END
ENDGO谢谢!
仔细看了下,如果游标是空,那实际执行的只有
SET @sql = 'DELETE FROM ODRECORD_Main WHERE GLIDEID = ''' + @GLIDEID + ''''
exec (@sql)
和
SET @sql = 'DELETE FROM ODRECORD WHERE ODID = ''' + @GLIDEID + ''''
exec (@sql)
看看ODRECORD_Main.GLIDEID和ODRECORD.ODID有无索引,没有加上应该能提高速度
还是删除1占时间多
还是删除2占时间多
在客户又一次出现问题的情况下,我,找到了问题出现的地方,时间几乎都在执行上头的一句,虽说这个游标是无数据集的。