• 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.