SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Split string into table, separator can be more than 1 char


Split string into table, separator can be more than 1 char

Author
Message
halford13
halford13
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 Visits: 147
Comments posted to this topic are about the item Split string into table, separator can be more than 1 char
phegel
phegel
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 58
I like your function, because you use a CTE.:-D

I've been learning how and when to use CTE's and this is just another item that has helped me to better understand them.

This funciton has been done before, but I haven't seen it using a CTE.

Thanks!
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94053 Visits: 38955
Not a bad solution but using a recursive CTE and a multi-statement TVF this function is not very scalable.

Here is another solution to this problem:


CREATE function [dbo].[DelimitedSplit] (
@pString varchar(max),
@pDelimiter char(1)
)
returns table
as
return
with
a1 as (select 1 as N union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1),
a2 as (select
1 as N
from
a1 as a
cross join a1 as b),
a3 as (select
1 as N
from
a2 as a
cross join a2 as b),
a4 as (select
1 as N
from
a3 as a
cross join a2 as b),
Tally as (select top (len(@pString))
row_number() over (order by N) as N
from
a4),
ItemSplit(
ItemOrder,
Item
) as (
SELECT
N,
SUBSTRING(@pDelimiter + @pString + @pDelimiter,N + 1,CHARINDEX(@pDelimiter,@pDelimiter + @pString + @pDelimiter,N + 1) - N - 1)
FROM
Tally
WHERE
N < LEN(@pDelimiter + @pString + @pDelimiter)
AND SUBSTRING(@pDelimiter + @pString + @pDelimiter,N,1) = @pDelimiter --Notice how we find the delimiter
)
select
row_number() over (order by ItemOrder) as ItemID,
Item
from
ItemSplit



Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35248 Visits: 11361
http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string-splitting-part-2-even-faster-even-more-scalable.aspx

FYI



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
The Dixie Flatline
The Dixie Flatline
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12838 Visits: 6903
It's a handy function, and a well written article. But, although I much prefer CTEs to subqueries, recursive CTEs are notoriously slow for this type of problem. Use of a tally table (also known as number table) for parsing delimited strings will do the same work in a fraction of the time, and there are even faster techniques in certain situations.

If you are not familiar with tally tables, it is really worth your while to become familiar with them. Jeff Moden's excellent article on the subject can be found here.

For an amazing group discussion about parsing delimited strings, look here.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
halford13
halford13
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 Visits: 147
Hi,

You're right, using the Tally table is faster:
using this parameter:
DECLARE @Parameter VARCHAR(8000)
SET @Parameter = REPLICATE('Element01,Element02,Element03,Element04,Element05,',159)
So I got next execution times (running the same script several times):
- my function: 120 ~ 180 miliseconds
- Lynn's function: 90 ~ 140 miliseconds
- Jeff's function (a function created based on Jeff's example, using Tally table (already created on my database) : 90 ~ 140 miliseconds


Thanks!


Thanks.
phegel
phegel
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 58
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)
,@OrderNo as 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
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94053 Visits: 38955
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)
,@OrderNo as 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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94053 Visits: 38955
halford13 (11/20/2009)
Hi,

You're right, using the Tally table is faster:
using this parameter:
So I got next execution times (running the same script several times):
- my function: 120 ~ 180 miliseconds
- Lynn's function: 90 ~ 140 miliseconds
- Jeff's function (a function created based on Jeff's example, using Tally table (already created on my database) : 90 ~ 140 miliseconds


Thanks!


Thanks.


I think you'll find that my function uses Jeff's split method, it just incorporates the tally table into the function itself.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
halford13
halford13
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 Visits: 147
Hi Lynn,

Yes, I saw you used Jeff's split method.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search