这条存储过程怎么样优化

[longyangyxm]

忽然发现程序中有一个存储过程 速度很慢
还能优化不 谢谢

CREATE PROCEDURE [dbo].[UpdateModifyOrder]
(
	@OrderDetailID int,
	@UpdateAmount int,
    @Operator varchar(20)
)
AS
Begin Tran 
DECLARE @RowCount int
SELECT @RowCount=COUNT(*) FROM TOrderDetail WHERE FOrderDetailID=@OrderDetailID
IF @RowCount>0
	BEGIN
        SELECT @RowCount=COUNT(*) FROM TModifyOrder WHERE FOrderDetailID=@OrderDetailID
        IF @RowCount>0
			BEGIN
			  UPDATE TModifyOrder SET FOperator=@Operator WHERE FOrderDetailID=@OrderDetailID	
			END
		ELSE
		   BEGIN
			  INSERT INTO TModifyOrder SELECT FOrderMasterID,FOrderDetailID,FBookID,FAmount,'',GETDATE()
									   FROM TOrderDetail WHERE FOrderDetailID=@OrderDetailID
			  UPDATE TModifyOrder SET FOperator=@Operator WHERE FModifyID=@@IDENTITY
		   END
		UPDATE TOrderDetail SET FAmount=@UpdateAmount
	END 
If @@Error <> 0
   Begin
     RollBack Tran
     Return -1
   End
Else
   Begin
     Commit Tran    
   End    

[-狙击手-]

IF exists(SELECT 1 FROM TOrderDetail WHERE FOrderDetailID=@OrderDetailID)
    BEGIN
        IF exists(SELECT 1 FROM TModifyOrder WHERE FOrderDetailID=@OrderDetailID
            BEGIN
              UPDATE TModifyOrder SET FOperator=@Operator WHERE FOrderDetailID=@OrderDetailID    
            END
        ELSE
           BEGIN
              INSERT INTO TModifyOrder SELECT FOrderMasterID,FOrderDetailID,FBookID,FAmount,'',GETDATE()
                                       FROM TOrderDetail WHERE FOrderDetailID=@OrderDetailID
              UPDATE TModifyOrder SET FOperator=@Operator WHERE FModifyID=@@IDENTITY
           END
        UPDATE TOrderDetail SET FAmount=@UpdateAmount
    END 

[sdhdy]

似乎对速度影响不大。

[sdhdy]

看看插入的表里，有触发器没有。

[longyangyxm]

[Quote=引用 3 楼 sdhdy 的回复:]
看看插入的表里，有触发器没有。
[/Quote]
你果然是触发器专家 一来就是触发器 里面没有触发器 (*^__^*) 嘻嘻……

[longyangyxm]

[Quote=引用 1 楼 happyflystone 的回复:]
SQL codeIF exists(SELECT 1 FROM TOrderDetail WHERE FOrderDetailID=@OrderDetailID)
BEGIN
IF exists(SELECT 1 FROM TModifyOrder WHERE FOrderDetailID=@OrderDetailID
BEGIN
UPDATE TModifyOrder SET FOperator=@Operator WHERE FOrderDetailID=@OrderDetailID
END
ELSE
BEGIN
INSERT INTO TModifyOrder SELECT FOrderMas…
[/Quote]
谢谢 学习了 去掉该死没意义的变量

[sdhdy]

O(∩_∩)O哈！

[longyangyxm]

[Quote=引用 6 楼 sdhdy 的回复:]
O(∩_∩)O哈！
[/Quote]
以前一阵子我问了个问题 http://topic.csdn.net/u/20090306/14/0d7e3633-ed31-4b83-b7f3-215c36605597.html
不过那次还真是触发器问题
还真谢谢你解决 那时你还不是触发器专家昵称
对你这头像影响很深 是K娃不？ 唱 我不想说我是鸡

[sdhdy]

不错，那个是我，这个昵称改了10天左右吧。

[longyangyxm]

[Quote=引用 2 楼 sdhdy 的回复:]
似乎对速度影响不大。
[/Quote]
MS我闯祸了 存储过程里有一条更新所有的数据的UPDATE TOrderDetail SET FAmount=@UpdateAmount
吓的我要死 幸好看看是在测试数据库上
难怪我觉得慢 郁闷
结帖