Maleficence
asked on
Stored procedure taking too long to execute
I have the following stored procedure
/* Procedure: SP_UpdateCustBalanceFromTr
Created: 1.00.????.0
Current: 1.00.????.0
Revision: 0
*/
IF EXISTS(SELECT * FROM sysobjects WHERE type='P' AND name='SP_UpdateCustBalance
DROP PROCEDURE SP_UpdateCustBalanceFromTr
go
CREATE PROCEDURE SP_UpdateCustBalanceFromTr
@CustomerID uniqueidentifier
, @CutOffDate DATETIME = NULL
AS
BEGIN
SET NOCOUNT ON
DECLARE
@Balance MONEY
, @Update BIT;
IF @CutOffDate IS NULL
BEGIN
SET @CutOffDate = GETDATE();
SET @Update = 1;
END
ELSE
BEGIN
SET @Update = 0;
END
SELECT @Balance = IsNull(sum(T.Amount),0)
FROM tbltransactions T
WHERE T.CustomerID = @CustomerID
AND T.TransDate < @CutOffDate
AND (
( 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,1
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
BEGIN
BEGIN TRANSACTION
UPDATE tblCustomers
SET balance = @Balance
WHERE CustomerID = @CustomerID;
IF @@Error <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN -1
END
ELSE
BEGIN
COMMIT TRANSACTION
RETURN 0
END
END
ELSE
BEGIN
SELECT @Balance as Balance
END
SET NOCOUNT OFF
END
GO
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?
/* Procedure: SP_UpdateCustBalanceFromTr
Created: 1.00.????.0
Current: 1.00.????.0
Revision: 0
*/
IF EXISTS(SELECT * FROM sysobjects WHERE type='P' AND name='SP_UpdateCustBalance
DROP PROCEDURE SP_UpdateCustBalanceFromTr
go
CREATE PROCEDURE SP_UpdateCustBalanceFromTr
@CustomerID uniqueidentifier
, @CutOffDate DATETIME = NULL
AS
BEGIN
SET NOCOUNT ON
DECLARE
@Balance MONEY
, @Update BIT;
IF @CutOffDate IS NULL
BEGIN
SET @CutOffDate = GETDATE();
SET @Update = 1;
END
ELSE
BEGIN
SET @Update = 0;
END
SELECT @Balance = IsNull(sum(T.Amount),0)
FROM tbltransactions T
WHERE T.CustomerID = @CustomerID
AND T.TransDate < @CutOffDate
AND (
( 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,1
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
BEGIN
BEGIN TRANSACTION
UPDATE tblCustomers
SET balance = @Balance
WHERE CustomerID = @CustomerID;
IF @@Error <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN -1
END
ELSE
BEGIN
COMMIT TRANSACTION
RETURN 0
END
END
ELSE
BEGIN
SELECT @Balance as Balance
END
SET NOCOUNT OFF
END
GO
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Xenon,
Thanks for pointing out that silliness with the transactions. I took it out of the code, but it didn't have an impact on performance. Still thanks for pointing that out. I was totally oblivious to the logical inconsistency there. ;)
namasi_navaretnam,
Balance is indeed type Money in the database. I did need to slap an index on TransSubStatusID which did not have an index on it. But peculiarly the slow down still occurred.
I also used your suggestion of imbedding the GETDATE() statements to help narrow down the culprit, which indicated that it was the beginning SELECT statement. Which as I already mentioned runs fast enough when I pop in by itself into Query Analyzer. I added the <= and >= suggestions as well, it helped with a few fractions of a second. So basically I was only getting a slowdown when I let SQL compile the code into a stored procedure.
However the problem finally solved itself when I put that Select into a function taking @CustomerId and @CutOffDate as parameters. That function ran as fast as the pure SQL did on Query Analyzer. So I modified the stored procedure to use the function instead and now it finishes in 0.1 seconds (much better than 17 seconds).
Thanks all for your help, even if you didn't provide the exact solution, you helped me see a few glitches in the code and helped me find a solution. I'll divide up the points.
For reference for others experiencing similar problems the final code looks like this:
CREATE FUNCTION FN_GetBalance(@CustomerID UNIQUEIDENTIFIER, @CutOffDate DATETIME)
RETURNS MONEY
AS
BEGIN
DECLARE
@Balance MONEY;
SELECT @Balance = Sum(IsNull(T.Amount, 0))
FROM tbltransactions T
WHERE T.CustomerID = @CustomerID
AND T.TransDate <= @CutOffDate
AND ( ( 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,1
OR ( T.TransStatusID = 0 AND T.TransType like 'Customer Comp(Refer%' )
OR ( T.TransStatusID in (49,50) AND T.TransSubStatusID in (2,5) )
)
RETURN @BALANCE
END
go
And the stored procedure now reads:
CREATE PROCEDURE SP_UpdateCustBalanceFromTr
@CustomerID uniqueidentifier
, @CutOffDate DATETIME = NULL
AS
BEGIN
SET NOCOUNT ON
DECLARE
@Balance MONEY
, @Update BIT;
IF @CutOffDate IS NULL
BEGIN
SET @CutOffDate = GETDATE();
SET @Update = 1;
END
ELSE SET @Update = 0;
SELECT @Balance = dbo.FN_GetBalance(@Custome
-- updates the customer's balance accordingly.
IF @Update = 1
BEGIN
UPDATE tblCustomers
SET balance = @Balance
WHERE CustomerID = @CustomerID;
IF @@Error <> 0 RETURN -1
ELSE RETURN 0
END
ELSE
SELECT @Balance as Balance
SET NOCOUNT OFF
END
GO
Thanks for pointing out that silliness with the transactions. I took it out of the code, but it didn't have an impact on performance. Still thanks for pointing that out. I was totally oblivious to the logical inconsistency there. ;)
namasi_navaretnam,
Balance is indeed type Money in the database. I did need to slap an index on TransSubStatusID which did not have an index on it. But peculiarly the slow down still occurred.
I also used your suggestion of imbedding the GETDATE() statements to help narrow down the culprit, which indicated that it was the beginning SELECT statement. Which as I already mentioned runs fast enough when I pop in by itself into Query Analyzer. I added the <= and >= suggestions as well, it helped with a few fractions of a second. So basically I was only getting a slowdown when I let SQL compile the code into a stored procedure.
However the problem finally solved itself when I put that Select into a function taking @CustomerId and @CutOffDate as parameters. That function ran as fast as the pure SQL did on Query Analyzer. So I modified the stored procedure to use the function instead and now it finishes in 0.1 seconds (much better than 17 seconds).
Thanks all for your help, even if you didn't provide the exact solution, you helped me see a few glitches in the code and helped me find a solution. I'll divide up the points.
For reference for others experiencing similar problems the final code looks like this:
CREATE FUNCTION FN_GetBalance(@CustomerID UNIQUEIDENTIFIER, @CutOffDate DATETIME)
RETURNS MONEY
AS
BEGIN
DECLARE
@Balance MONEY;
SELECT @Balance = Sum(IsNull(T.Amount, 0))
FROM tbltransactions T
WHERE T.CustomerID = @CustomerID
AND T.TransDate <= @CutOffDate
AND ( ( 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,1
OR ( T.TransStatusID = 0 AND T.TransType like 'Customer Comp(Refer%' )
OR ( T.TransStatusID in (49,50) AND T.TransSubStatusID in (2,5) )
)
RETURN @BALANCE
END
go
And the stored procedure now reads:
CREATE PROCEDURE SP_UpdateCustBalanceFromTr
@CustomerID uniqueidentifier
, @CutOffDate DATETIME = NULL
AS
BEGIN
SET NOCOUNT ON
DECLARE
@Balance MONEY
, @Update BIT;
IF @CutOffDate IS NULL
BEGIN
SET @CutOffDate = GETDATE();
SET @Update = 1;
END
ELSE SET @Update = 0;
SELECT @Balance = dbo.FN_GetBalance(@Custome
-- updates the customer's balance accordingly.
IF @Update = 1
BEGIN
UPDATE tblCustomers
SET balance = @Balance
WHERE CustomerID = @CustomerID;
IF @@Error <> 0 RETURN -1
ELSE RETURN 0
END
ELSE
SELECT @Balance as Balance
SET NOCOUNT OFF
END
GO
Anyone know why putting just a sum select in a function will speed things up?
I am having a similar issue with a SP where I have 10 nested select statements, the sums take forever to run. The whole SP takes 3-4 minutes to execute.
Matt
I am having a similar issue with a SP where I have 10 nested select statements, the sums take forever to run. The whole SP takes 3-4 minutes to execute.
Matt
mpest
here is what I think it is:
when u create a SP, MSSQL will create a execution plan...which will than be reused whenever that SP is called..... Now the problem is that that select has parameters, so depending on those parameters, the execution may be different (I mean the plan for executing that query may vary, once using an index, other time other index). Now the problem is that no matter how many times you will use that SP (if is not changed) the execution plan will be the same, so it will not use different indexes as it should do.....
hope I expressed myself prety well....
Now puting the select inside a function with those parameters, will force MSSQL to recreate the plan each time, depending on those parameters....
regards,
xenon
here is what I think it is:
when u create a SP, MSSQL will create a execution plan...which will than be reused whenever that SP is called..... Now the problem is that that select has parameters, so depending on those parameters, the execution may be different (I mean the plan for executing that query may vary, once using an index, other time other index). Now the problem is that no matter how many times you will use that SP (if is not changed) the execution plan will be the same, so it will not use different indexes as it should do.....
hope I expressed myself prety well....
Now puting the select inside a function with those parameters, will force MSSQL to recreate the plan each time, depending on those parameters....
regards,
xenon
Ah, xenon.
I think I understand what you are saying. The SP will have an execution plan with a static set of indexes that will not change throughout the whole SP. The sum call in the SP will use these indexes. If in a function, the function will have a different execution plan thus a different set of indexes when it runs so it could be faster.
Thanks for the help understanding. I like to know why as well as how...
Matt
I think I understand what you are saying. The SP will have an execution plan with a static set of indexes that will not change throughout the whole SP. The sum call in the SP will use these indexes. If in a function, the function will have a different execution plan thus a different set of indexes when it runs so it could be faster.
Thanks for the help understanding. I like to know why as well as how...
Matt
You need to issue a begin transaction only if there are more operations that you want to be executed all or none...not only 1 sql statement... read on BOL more regarding this.....
Also the amount of time the update takes is dependet on the indexes....so if you don't have any index on CustomerID on the tblCustomer table (which I doubt being the id) than a table scan will be made, and this may take a lot of time for big tables...
regards,
xenon