Solved

Stored procedure taking too long to execute

Posted on 2003-10-23
8
821 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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
 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
t-sql Joining Issue 10 40
SQL Replication question 9 43
Find SQL query used by application 3 20
[SQL server / powershell] bulk delete table from CSV 8 33
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

810 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