SQL Server Performance Forum – Threads Archive
Beware Row-by-Row Operations in UDF ClothingHey Guys, See this article… Beware Row-by-Row Operations in UDF Clothing
This is a true sign that sometimes all that glitters is not gold. From the perspective of performance, I have always been sceptical about UDFs. I use them only for small routines that don’t involve accessing rows from any tables – Like creating my own string manipulation and formatting functions not supplied in SQL server.
Yes, Ive always been skeptical too. But I use them in the same way that you do, but also they are invaliable for returning a resultset to JOIN onto. Using them this way wont suffer from the problems highlighted in the article. UDF’s which take a paremeter and return a single value, can often be rewritten to not take a value, and return a resultset of the same data, which can then form part of a JOIN. This is much more efficient as it does not operate row-by-row.
UDFs have still not convinced me of their place within SQL server as opposed to stored procedures. The many rules (and equally numerous exceptions) associated with their implementation have denied me the chance to embrace them fully as a new and important feature in SQL2K.
To me, UDF’s are invaluable for routines where you need to specify a variable length array of parameters. Heres one I rolled earlier.. ———————————————————————–
— Function: dbo.GetResultSetFromIntList
— Given a string containing a comma delimited list of integers
— returns a TABLE containing those integers, one per row, allowing it
— to be used as part of a JOIN
— 20/05/2003.PAC – Initial Version
CREATE FUNCTION dbo.GetResultSetFromIntList
RETURNS @CsvResults TABLE (csvcode int)
DECLARE @off int
DECLARE @substr varchar(4000)
DECLARE @nextValue int
DECLARE @prevOff int SELECT @prevOff = 1
SELECT @off = CHARINDEX(‘,’, @csvString, 0) WHILE (@off > 0)
SELECT @nextValue = CONVERT(int, SUBSTRING(@csvString, @prevOff, @[email protected]))
SELECT @prevOff = @off+1
insert into @CsvResults (csvcode) Values (@nextValue)
SELECT @off = CHARINDEX(‘,’, @csvString, @prevoff)
END — if string did not terminate with a trailing comma
SELECT @nextValue = CONVERT(int, SUBSTRING(@csvString, @prevOff, LEN(@csvString)[email protected]+1))
insert into @CsvResults (csvcode) Values (@nextValue) return
END Then you can simply JOIN onto the result set like this… select * from dbo.GetResultSetFromIntList(‘1,132,1333’) LEFT JOIN DictVehicles ON (csvCode = DI_TextCode)
Very useful! and performance for these sort of operations is good too.
Pretty cool stuff. Forgive me if I seem to get the impression that irrespective of what goes on within the UDF body, the ultimately useful result is its usage in JOINS. Thanks.
If you like arrays there’s a very indepth article here
HTH Jasper Smith
Quite an eye opener there(together with the links within). Thanks. NHO