Wednesday, December 24, 2008

Inline Scalar Function Performance (T-SQL geeks only)

If you are technical and work alot with SQL Server, I highly recommend subscribing to SQL Server Magazine. One of the contributing authors, Itzik Ben-Gan, is a T-SQL expert in the extreme.

Itzik recently posted an excellent article about performance issues using inline scalar functions (functions that return only 1 value and are used within a larger query). He demonstrates the performance of a typical inline scalar function, and then offers a relatively simple alternative approach that dramatically improves performance.

http://www.sqlmag.com/articles/index.cfm?articleid=101104&

In his sample, he demostrates how this change makes his 14 second query run in less than 1 second. That's a massive performance improvement for changing just a few keywords that will not affect the structure of your query.

Since I wrote an earlier post about UDFs, I thought that this would be a great follow up. To summarize his post:

1) Add the "RETURNS TABLE" clause to your UDF
2) Change your inline function call to use a SELECT statement against the function

Well worth the minimal effort, even if you aren't querying a million rows.

No comments: