Update SQL within Procedure perform Slow

• anupamk

  SSC-Addicted

  Points: 442

  More actions

  December 26, 2013 at 5:19 am

  #286506

  Hi,

  I have 5-6 Update SQL within a Procedure. These Update SQL are 3-4 liner and are updating Columns based on some other Column in same table and some are based on join.

  eg.

  1. UPDATE Customer SET PhoneNumber='N/A' WHERE PhoneNumber=''

  2. UPDATE Customer SET Salary=sa.Salary FROM Customer cust JOIN Salary sa ON cust.CustomerID=Sa.CustomerID

  These update are taking very long time to complete in Procedure, approx 15 minutes. Where as if i execute them separately it takes only 2-3 minutes.

  What could be reason?

  Any thoughts appreciated.

  Thanks,

  Anupam

• Jeff Moden

  SSC Guru

  Points: 1004366

  More actions

  December 26, 2013 at 5:48 am

  #1675933

  anupamk (12/26/2013)

  ---

  Hi,

  I have 5-6 Update SQL within a Procedure. These Update SQL are 3-4 liner and are updating Columns based on some other Column in same table and some are based on join.

  eg.

  1. UPDATE Customer SET PhoneNumber='N/A' WHERE PhoneNumber=''

  2. UPDATE Customer SET Salary=sa.Salary FROM Customer cust JOIN Salary sa ON cust.CustomerID=Sa.CustomerID

  These update are taking very long time to complete in Procedure, approx 15 minutes. Where as if i execute them separately it takes only 2-3 minutes.

  What could be reason?

  Any thoughts appreciated.

  Thanks,

  Anupam

  Large rowcount, little memory.

  Blocking.

  Lack of indexes.

  Too many indexes.

  Etc.

  Etc.

  There are dozens of possible reasons. Please see the second link under "Helpful Links" in my signature line below if you want help with specific queries.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)