Post reply

Surely It Should Just Work?

  • November 19, 2009 at 11:22 am

    #1081749

    bpatin (11/19/2009)

    I've only worked on my own functions, and have looked at execution plans to compare the with and without. I have created some queries, without using functions, that had to incorporate some very complex multiple CASE statements that made the query very hard to understand and maintain. A simple function greatly simplified the query, and the execution plans and actual use showed no difference in the performance.

    So, I assume performance is highly dependent on the function and how it is used.

    Yes, performance it is very dependent on what the function does. The main disasterous-performance-causing issue (although there are others) is when a UDF does ancillary data access. Using such function in a query almost invariably leads to row-by-row processing of said query. Another is joining to UDFs which give the optimizer an estimated rowcount of 1, leading the optimizer to pick nested loop plans even though you might hit 100M rows in other tables - pack a lunch.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing post 31 (of 30 total)

You must be logged in to reply to this topic. Login to reply