SQL Server Performance Forum – Threads Archive

Joins vs User defined function

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

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
http://www.SQL-Server-Performance.Com
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.

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

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? 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=’

‘ />] – Frank Kalis Microsoft SQL Server MVP Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a> Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

]]>