Update Statement - conditional

• Mahesh Bote

  SSCrazy Eights

  Points: 9107

  More actions

  October 7, 2015 at 3:17 am

  #399149

  [font="Verdana"]Hi All,

  I have a situation. I have one conditional UPDATE statement in stored procedure which updates one column based on other columns in same row. To fulfill the required we have written one function which accepts other columns and returns the value which get updated against the expected column. The problem here is, the whole update is taking too much time to execute. We are checking the optimization possibilities however we have not got any idea.

  Can anyone suggest me, what other remedies can be applied to tune the UPDATE statement?

  Thanks in advance,

  -- Mahesh[/font]

  MH-09-AM-8694

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 7, 2015 at 3:20 am

  #1831937

  Mahesh Bote (10/7/2015)

  ---

  [font="Verdana"]Hi All,

  I have a situation. I have one conditional UPDATE statement in stored procedure which updates one column based on other columns in same row. To fulfill the required we have written one function which accepts other columns and returns the value which get updated against the expected column. The problem here is, the whole update is taking too much time to execute. We are checking the optimization possibilities however we have not got any idea.

  Can anyone suggest me, what other remedies can be applied to tune the UPDATE statement?

  Thanks in advance,

  -- Mahesh[/font]

  Can you post the actual execution plan please? Without it, folks will be guessing.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Grant Fritchey

  SSC Guru

  Points: 398690

  More actions

  October 7, 2015 at 6:38 am

  #1831972

  Absolute agreement with Chris. We'd need to see the query, the function and the execution plan to make an assessment.

  If it's a multi-statement, table-valued, user-defined function, that's problematic right there.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning