phegel (11/20/2009)
This is the function that I am currently using, is it more efficient than the CTE ?
create FUNCTION [dbo].[func_CreateClientTableOrdered]
(
@item_list as varchar(4000)
)
RETURNS @Items Table(OrderNo int, Item varchar(60))
AS
BEGIN
declare @separator as char(1)
,@position as int
,@item as varchar(60)
,@OrderNoas int
set @OrderNo = 0
SET @separator = ','
SET @item_list = @item_list + @separator
SELECT @position = patindex('%'+@separator+'%', @item_list)
WHILE @position <> 0
BEGIN
SELECT @Item = left(@item_list, @position - 1)
SELECT @item_list = stuff(@item_list, 1, @position, '')
SELECT @position= patindex('%'+@separator+'%', @item_list)
INSERT @Items(OrderNo, Item)
SELECT @OrderNo, @Item
SELECT @OrderNo= @OrderNo + 1
END
RETURN
END
Can someone give me a good way of proving which function would perform better?
Or point me to an article on performance tuning functions. Thanks, Paul
First, look up in this thread, I posted a function that should perform better. Also, there is a link to another thread that contains a very good discussion on string parsing.
I can see two issues with your function. One, it is using a multi-statement TVF. Second, it is using a while loop. Both of these will keep your function from scaling well.