I have the following stored procedure
/* Procedure: SP_UpdateCustBalanceFromTrans
IF EXISTS(SELECT * FROM sysobjects WHERE type='P' AND name='SP_UpdateCustBalanceFromTrans')
DROP PROCEDURE SP_UpdateCustBalanceFromTrans
CREATE PROCEDURE SP_UpdateCustBalanceFromTrans
, @CutOffDate DATETIME = NULL
SET NOCOUNT ON
, @Update BIT;
IF @CutOffDate IS NULL
SET @CutOffDate = GETDATE();
SET @Update = 1;
SET @Update = 0;
SELECT @Balance = IsNull(sum(T.Amount),0)
FROM tbltransactions T
WHERE T.CustomerID = @CustomerID
AND T.TransDate < @CutOffDate
( T.Amount > 0 AND T.TransStatusID in (6,7,18,20,22,26,32,33,34) )
OR ( T.Amount < 0 AND T.TransStatusID in (1,2,4) )
OR ( T.TransStatusID in (5,8,9,10,11,12,13,14,15,16,23,24,25,38,39,40,41,42,43) )
OR ( T.TransStatusID = 0 AND T.TransType like 'Customer Comp(Refer%' )
OR ( T.TransStatusID in (49,50) AND T.TransSubStatusID in (2,5) )
-- updates the customer's balance accordingly.
IF @Update = 1
SET balance = @Balance
WHERE CustomerID = @CustomerID;
IF @@Error <> 0
SELECT @Balance as Balance
SET NOCOUNT OFF
When I run the stored procedure it takes about 17 seconds to run, which is unacceptable.
However if I take the T-SQL statements outside and run them independently in Query Analyzer they take 0 seconds to execute. The Execution plan is useless saying that the stored procedure is less system intensive, but it keeps taking longer to execute than the raw SQL.
So far I've managed to track the slowdown to the BEGIN TRANSACTION and subsequent UPDATE statement. If I remove that segment of the stored procedure then it runs smoothly. Even leaving the update in there by itself causes the significant slow down. A select on tblCustomers by CustomerID is near instantenous, hence I'm inclined to think it's not a matter of indexes as that's the primary key with a clustered index on it.
Is there any way to optimize this process so it doesn't slow down so much?