UDF Vs Subquery performance

• SQL*

  SSCertifiable

  Points: 6638

  More actions

  October 17, 2010 at 10:25 pm

  #147376

  How to make use of UDF or subquery?

  Which is better one with performance considerations?

  thanks

  🙂

• Jeff Moden

  SSC Guru

  Points: 1004410

  More actions

  October 17, 2010 at 11:07 pm

  #1238227

  SQL* (10/17/2010)

  ---

  How to make use of UDF or subquery?

  Which is better one with performance considerations?

  thanks

  "It Depends". You have to test each way based on the situation you're trying to solve. Also keep in mind all of the choices you have...

  Scalar Function

  Multiline Table Valued Function (mlTVF)

  Inline Table Valued Function (iTVF)

  CROSS APPLY

  Correlated Subquery

  Subquery

  Join

  Most people will tell you to avoid the Scalar and mlTVF types of functions. They'll also tell you to avoid the Correlated Subquery. I'll tell you, "It Depends". I've seen where iTVF's can create quite the performance problem and where they can be miracles of performance, for example.

  --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)
  

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  October 17, 2010 at 11:08 pm

  #1238229

  Test both and see which performs better?

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass