Solved

Stored procedure taking too long to execute

Posted on 2003-10-23
8
810 Views
Last Modified: 2012-08-13
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?

0
Comment
Question by:Maleficence
8 Comments
 
LVL 9

Assisted Solution

by:xenon_je
xenon_je earned 150 total points
ID: 9611903
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
 
LVL 9

Expert Comment

by:xenon_je
ID: 9611910
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
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 150 total points
ID: 9612374
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
 
LVL 15

Accepted Solution

by:
namasi_navaretnam earned 200 total points
ID: 9613573
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:Maleficence
ID: 9616259
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
 
LVL 1

Expert Comment

by:mpest
ID: 10051919
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
 
LVL 9

Expert Comment

by:xenon_je
ID: 10081795
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
 
LVL 1

Expert Comment

by:mpest
ID: 10082193
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now