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:
Post a Comment