SQL Server Performance

Joins vs User defined function

Discussion in 'T-SQL Performance Tuning for Developers' started by parthkashyap, Jun 7, 2007.

  1. parthkashyap New Member

    Which of the 2 is more effecient ??
    Using multiple joins in the query or using user functions to returna value
  2. satya Moderator

    If the UDF is scalar that means it will be called once for each row in the result set. And for each call, a seperate read in the lookup table has to be performed. For the query above, the execution plan MIGHT be the same - but it need not. The optimizer will consider the various indexes available, check some statistics and then create an execution plan with the best expected
    exectution speed.

    SO you can test is yourselves for the differences on the execution plan between join & UDF.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  3. Adriaan New Member

    As a rule, adding a UDF call to a column in a JOIN expression or a WHERE clause means you're killing performance.
  4. FrankKalis Moderator

    Sorry, but I don't get the question. Are you asking if moving multiple JOINs from s straight statement to a UDF to "hide" the JOINs in the UDF, make a statement more effective?<br /><br />I don't think so, but it might make your statement more readable. Not the best reason to go down that road, if you ask me. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Share This Page