XML Query is very slow

Question

Hi Declare @String varchar(max) = '123456789'  @Delimiter char(1) = '' SELECT T.split.value('.' 'nvarchar(max)') AS data FROM ( SELECT CAST(' ' + REPLACE(Replace(Replace(@String'&''&')' ') + ' ' AS XML) as T1 ) as XMLData CROSS APPLY T1.nodes('/d') T (split) here @String have 9 data when @String have more than 2000 data at that time Query get very slow . is there any ohter solution to improve this query. Thank you.

Jignesh Trivedi · Answer

hi Harshil That I understand but i want to know what reason behind using cast and replace? ok Same thing u can acheive by fn_split which is in build in SQL-2008 Declare @String varchar(max) = '123456789'  @Delimiter char(1) = '' select Value from dbo.fn_Split(@String@Delimiter) And Defination of fn_split CREATE FUNCTION [dbo].[fn_Split] ( @ListValues nvarchar(max) @SplitBy nvarchar(5) ) RETURNS @ResultValue table ( Id int identity(11) Value nvarchar(100) ) AS BEGIN While (Charindex(@SplitBy@ListValues)>0) Begin Insert Into @ResultValue (value) Select Value = ltrim(rtrim(Substring(@ListValues1Charindex(@SplitBy@ListValues)-1))) Set @ListValues = Substring(@ListValuesCharindex(@SplitBy@ListValues)+len(@SplitBy)len(@ListValues)) End Insert Into @ResultValue (Value) Select Value = ltrim(rtrim(@ListValues)) Return END hope this help.

Harshil Shah · Answer

Hi Declare @String varchar(max) = '123456789'  @Delimiter char(1) = '' SELECT T.split.value('.' 'nvarchar(max)') AS data FROM ( SELECT CAST(' ' + REPLACE(Replace(Replace(@String'&''&')' ') + ' ' AS XML) as T1 ) as XMLData CROSS APPLY T1.nodes('/d') T (split) above query is spliting data by delimiter. when string have more than 2000 data(123.....19992000) at that time this query get slow. is there any solution to improve this query. Thank you.

Jignesh Trivedi · Answer

yes my friend but I want what is an actual input and required out put so we can rewrite query in dirrent way to improve performance. In first glance i can not understand what is actual out required.

brunda k · Answer

I think they explained their requirment when @String have more than 2000 data at that time Query get very slow

Jignesh Trivedi · Answer

Hi Can please expain what is your requirement? Base on you Requirement we can write query.

brunda k · Answer

Declare @String varchar(max)  @Delimiter char(1) set @String ='1;2;3;4;5;6;7;8;9' set @Delimiter = ';' SELECT T.split.value('.' 'nvarchar(max)') AS data FROM ( SELECT CAST(' ' + REPLACE(@String @Delimiter ' ') + ' ' AS XML) as T1 ) as XMLData CROSS APPLY T1.nodes('/d') T (split)