Stored procedure taking too long to execute

I have the following stored procedure

/* Procedure:  SP_UpdateCustBalanceFromTrans
      Created:    1.00.????.0
      Current:    1.00.????.0
      Revision:   0
*/

IF EXISTS(SELECT * FROM sysobjects WHERE type='P' AND name='SP_UpdateCustBalanceFromTrans')
      DROP PROCEDURE SP_UpdateCustBalanceFromTrans
go

CREATE PROCEDURE SP_UpdateCustBalanceFromTrans
  @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,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
    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?

MaleficenceAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

xenon_jeCommented:
strange what you do.....

an update statement is a tranaction itself...so you don't need to do a begin transaction at all.....

so you should do :


CREATE PROCEDURE SP_UpdateCustBalanceFromTrans
  @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,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
    BEGIN
     
      UPDATE tblCustomers  
      SET balance = @Balance
      WHERE CustomerID = @CustomerID;
     
      IF @@Error <> 0 return -1
      else return 0
  END
  ELSE
    BEGIN
      SELECT @Balance as Balance
    END
   
  SET NOCOUNT OFF
END
GO

good luck,
  xenon
0
xenon_jeCommented:
in other words, if you issue an update, and that update fails, than the rollback is made automaticaly, and if its succeded than the commit is made...so you code is wrong....

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
0
LowfatspreadCommented:
Hi Maleficence,

i agree with xenon, you don't appear to need to
use a transaction around the update...

you could also just do the update
without the prior select
using a join in the update statement...  


UPDATE tblCustomers  
      SET balance = t.Balance
from tblcustomers as C
 Inner Join (

 SELECT CustomerID,sum(T.Amount) as Bal
  FROM tbltransactions T
  WHERE 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,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) )
          )
      group by Customerid
      ) as T
       on C.CustomerID=T.CustomerID
      WHERE C.CustomerID = @CustomerID
         

Although without having seen you query plan  
i'd have thought that the "Nested" or's where the culprit


Cheers!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

namasi_navaretnamCommented:
Throw some getdate() staments to see which sql takes too long. We can then focus on that sql.

select getdate()

Some Hints:
Also make sure there are indexes on TransStatusID, TransType , TransSubStatusId
Also I think
SELECT @Balance = IsNull(sum(T.Amount),0)
Should be
SUM( IsNull(T.Amount, 0) )

Also make sure Balance is defined as MONEY in db, Otherwise use the same datatype.
If this proc was created long ago drop and re-create
>= perfoems better than > , But I am not sure if you can change that.


Example:

CREATE PROCEDURE SP_UpdateCustBalanceFromTrans
  @CustomerID   uniqueidentifier
, @CutOffDate    DATETIME = NULL
AS
BEGIN
  SET NOCOUNT ON
  DECLARE
    @Balance MONEY
  , @Update  BIT;
 
  select getdate()  

  IF @CutOffDate IS NULL
    BEGIN
      SET @CutOffDate = GETDATE();
      SET @Update = 1;
    END
  ELSE
    BEGIN
      SET @Update = 0;
    END

   select getdate()  
 
  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,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) )
          )

  select getdate()  

         
  -- updates the customer's balance accordingly.
 
  IF @Update = 1
    BEGIN
     
      select getdate()  

      UPDATE tblCustomers  
      SET balance = @Balance
      WHERE CustomerID = @CustomerID;

     
      IF @@Error <> 0 return -1
      else return 0
  END
  ELSE
    BEGIN
      SELECT @Balance as Balance
    END

  select getdate()  

   
  SET NOCOUNT OFF
END
GO
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MaleficenceAuthor Commented:
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,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) )
            )
  RETURN @BALANCE
END
go

And the stored procedure now reads:

CREATE PROCEDURE SP_UpdateCustBalanceFromTrans
  @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(@CustomerID, @CutOffDate)
  -- 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
0
mpestCommented:
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
0
xenon_jeCommented:
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
0
mpestCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.