SQL Server Performance

Simple Function Performance

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by hominamad, May 21, 2008.

  1. hominamad Member

    I always went under the assumptions that while you had to be very careful when using user defined functions, very simple ones would have little or no performance impact in general. I did a test today that showed this to be wrong.
    I wanted to write a function that strips out the domain from the user name and returns it. The logic is:right(
    suser_name(), len(suser_name()) - charindex('', suser_name()))
    I did a test and updated all rows in a table with 450,000 records. When I used the expression in the UPDATE, I saw a +100% peformance improvment over using the function. The function only contains one line and its the same as above.
    Does this make sense? I don't understand why there would be such a huge difference. The execution plans are virtually the same, except the function has a table spool which is 0% of the execution plan.
    So what's going on here?
  2. Adriaan New Member

    If you include the expression in the UPDATE query, then SQL knows it only has to evaluate suser_name() one time for all the rows that will be updated.
    With the UDF, SQL has to evaluate suser_name() for each row all over again.
    Don't waste server time and resources!

Share This Page