split performance

Question

split performance

sathiyan00

SSCommitted

Points: 1879
More actions
October 8, 2013 at 5:46 am

#280604

Hi all i have tried below two functions to split string,but both are working slow:
Function 1 using substring and while loop
ALTER function [dbo].[uf_split](
@String nvarchar (MAX),
@Delimiter nvarchar (10)
)
returns @ValueTable table ([Value] nvarchar(MAX))
begin
declare @NextString nvarchar(MAX)
declare @Pos int
declare @NextPos int
declare @CommaCheck nvarchar(1)
--Initialize
set @NextString = ''
set @CommaCheck = right(@String,1)
--Check for trailing Comma, if not exists, INSERT
--if (@CommaCheck <> @Delimiter )
set @String = @String + @Delimiter
--Get position of first Comma
set @Pos = charindex(@Delimiter,@String)
set @NextPos = 1
--Loop while there is still a comma in the String of levels
while (@pos <> 0)
begin
set @NextString = substring(@String,1,@Pos - 1)
insert into @ValueTable ( [Value]) Values (@NextString)
set @String = substring(@String,@pos +1,len(@String))
set @NextPos = @Pos
set @pos = charindex(@Delimiter,@String)
end
return
end
FUNCTION 2 with XML:
ALTER function [dbo].[split] (
@qustion_option_id varchar(max),
@separator char )
RETURNS @list TABLE (item varchar(max))
BEGIN
DECLARE @xml XML
SELECT @xml = CAST ( '<A>' + REPLACE ( @qustion_option_id, @separator, '</A><A>') + '</A>' AS XML)
INSERT
INTO @list
SELECT t.value('.', 'varchar(max)')
FROM @xml.nodes('/A') AS xml(t)
RETURN
END
any other way to do the split function efficiently?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Not only a faster solution, but a comparison of many including the two you've listed: DelimitedSplit8k[/url] by Jeff Moden et al.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sathiyan00 SSCommitted Points: 1879 More actions · Answer 2

but even this is also taking more time for 25K records.

with Improved SQL 8K “CSV Splitter” Function its taking around 25 sec

with xml its taking around 30sec

with substring its taking more than one min

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

sathiyan00 (10/8/2013)
but even this is also taking more time for 25K records.
with Improved SQL 8K “CSV Splitter” Function its taking around 25 sec
with xml its taking around 30sec
with substring its taking more than one min

Post your query.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

sathiyan00 (10/8/2013)
but even this is also taking more time for 25K records.
with Improved SQL 8K “CSV Splitter” Function its taking around 25 sec
with xml its taking around 30sec
with substring its taking more than one min

On your functions, you're using varchar(max) and it's noted on the article that it should be avoided because it will reduce performance of the 8KDelimitedSplitter.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

split performance

Cookies on SQLServerCentral