December 28, 2012 at 1:04 am
Hi,
Can you please test your new functions' performance against the following function :
CREATE FUNCTION [dbo].[ParseString]
(
@String VarChar(8000),
@Delimiter VarChar(1)
) RETURNS TABLE
AS
RETURN
(
WITH Tokens(LinePos, StartPos, EndPos) AS
(
SELECT
1,
1,
CharIndex(@Delimiter, @String)
UNION ALL
SELECT
LinePos + 1,
EndPos + 1,
CharIndex(@Delimiter, @String, EndPos + 1)
FROM
Tokens
WHERE
EndPos > 0
)
SELECT
CONVERT(Int, LinePos) AS RowNumber,
SubString
(
@String,
StartPos,
CASE
WHEN EndPos > 0 THEN (EndPos - StartPos)
ELSE 8000
END
)
AS StringValue
from Tokens
)
and let us know which performs best ?
Regards,
Dirk van der Watt
December 28, 2012 at 7:55 am
For anybody just stumbling in here, this was a response posted in another thread. http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url]
For the sake of anybody else coming here please see the link I referred to above. It goes into great detail how to split strings and tests the performance of a number of different approaches.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 28, 2012 at 8:04 am
Dirk vd Watt (12/28/2012)
Hi,Can you please test your new functions' performance against the following function :
CREATE FUNCTION [dbo].[ParseString]
(
@String VarChar(8000),
@Delimiter VarChar(1)
) RETURNS TABLE
AS
RETURN
(
WITH Tokens(LinePos, StartPos, EndPos) AS
(
SELECT
1,
1,
CharIndex(@Delimiter, @String)
UNION ALL
SELECT
LinePos + 1,
EndPos + 1,
CharIndex(@Delimiter, @String, EndPos + 1)
FROM
Tokens
WHERE
EndPos > 0
)
SELECT
CONVERT(Int, LinePos) AS RowNumber,
SubString
(
@String,
StartPos,
CASE
WHEN EndPos > 0 THEN (EndPos - StartPos)
ELSE 8000
END
)
AS StringValue
from Tokens
)
and let us know which performs best ?
Regards,
Dirk van der Watt
Don't really need to, this is a recursive cte type splitter. Pretty confident that the DelimitSplit8K will out perform it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply