Use of SCHEMABINDING option for TSQL UDFs can improve performance in SQL Server 2005...

  • Article

SQL Server 2005 has new optimization logic to use the SCHEMABINDING option to derive certain properties about the TSQL UDF. This can greatly improve performance of queries that use scalar UDFs in a SELECT statement for example. You can read all about it at the blog entry below from one of our development engineers.

https://blogs.msdn.com/sqlprogrammability/archive/2006/05/12/596424.aspx

Comments

  • Anonymous
    June 20, 2006
    The use of Schemabinding improves performance with SQL 2005 UDF's...

    I think there has been additional improvements in relation to the way in which UDF's are processed in SQL2005.

    We have been testing an application that makes heavy use of transact SQL functions
    and have noticed vast improvements. (14 hours SQL Server 2000 SP4) to (40 minutes SQL Server 2005 SP1).

    All testing was done on exactly the same hardware etc etc...

    The UDF's are performing string comparisons and manipulations against tables that have millions of rows.

    Why has performance improved so much???

    One thing we found when examining a profile trace of the UDF call is that in SQL 2000 the "SP:ExeContextHit" event
    gets fired every time the function is called.

    However in SQL 2005 the "SP:CacheHit" event only gets fired once.....

    so....

    select myfunction(someColumn)
    from   sometable  ---------------  ( assume sometable has 500 rows)

    will generate 500 "SP:ExeContextHit" events in SQL2000 but only 1 "SP:CacheHit" event in SQL2005

    Regards,

    Andrew
  • Anonymous
    July 06, 2006
    Well, that would explain why my IsNull almost-dynamic WHERE clauses would suddenly start working so much faster than they did in SQL Server 2000!

    Thanks!