Using XML to Enhance the Performance of String Manipulations

  • WayneS (8/20/2008)


    Paul DB (8/20/2008)


    anil_mootha (8/20/2008)


    Hi,

    Why would you prefer to build XML strings manually when you can do it with FOR XML statement.

    The very purpose of FOR XML statement would be void otherwise.

    Thanks and regards

    Anil

    I'm also interested in knowing the answer to Anil's question. Have a great day. 😎

    Pure and simple laziness. In the article, I mentioned I was troubleshooting a performance issue. I was checking to see if the problem was in the processing of the xml string (not the creating of it). I just wanted something quick and dirty to build a string so that I could test it. (Turned out to not be so quick... ) I don't use the FOR XML clause that frequently, so when I do I usually need to check it's syntax to get it right.

    I am also not familiar with FOR XML. 🙂 Could anyone show us how one of Wayne's queries would look using FOR XML instead? I'd offer 1 point, if I could. 😉

    Paul DB

  • Paul DB (8/20/2008)


    WayneS (8/20/2008)


    Paul DB (8/20/2008)


    anil_mootha (8/20/2008)


    Hi,

    Why would you prefer to build XML strings manually when you can do it with FOR XML statement.

    The very purpose of FOR XML statement would be void otherwise.

    Thanks and regards

    Anil

    I'm also interested in knowing the answer to Anil's question. Have a great day. 😎

    Pure and simple laziness. In the article, I mentioned I was troubleshooting a performance issue. I was checking to see if the problem was in the processing of the xml string (not the creating of it). I just wanted something quick and dirty to build a string so that I could test it. (Turned out to not be so quick... ) I don't use the FOR XML clause that frequently, so when I do I usually need to check it's syntax to get it right.

    I am also not familiar with FOR XML. 🙂 Could anyone show us how one of Wayne's queries would look using FOR XML instead? I'd offer 1 point, if I could. 😉

    Paul,

    Re-read the article. The first two examples show an XML method that use the FOR XML clause.

    Wayne

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Hi Paul,

    It looks like nobodys bothered to answer our question. To me till I dont get a satisfactory answer, this topic doesnt make any sense!

    Have a wonderful day!

  • I really good performance improvement on string concatenation. I guess these problems weren't present in SQL 2000 so much as we didn't have 2GB varchar variables to work with and had to find other solutions 🙁

    However there are still merits in looking into other options of rowset manipulation as string concatenation and csv strings should really be a last resort and not part of the main toolset as we've had programmers utilise csv strings to have their code break when commas are included in the data.

    As a note I think it should be stated that this code is for SQL 2005 and up.

    Thanks for a good article.

  • Very useful.

    I use it to process a comma delimited list of keys or any other list of values that is passed as a parameter to a stored proc.

    Now I do not have to worry about the string being too long, Mine will never be as big as 31k list of values/keys.

    My function to split the string up and return a table(that can be used in joins etc) now looks like this :

    :angry: See attachment cos I cannot get xml tags to display correctly :blush:

  • anil_mootha (8/20/2008)


    Hi Paul,

    It looks like nobodys bothered to answer our question. To me till I dont get a satisfactory answer, this topic doesnt make any sense!

    Have a wonderful day!

    I thought I had answered the question... I'm sorry if I haven't.

    I had seen two questions:

    1. Why would anyone build an XML string manually instead of using FOR XML?

    2. Could anyone show us how one of Wayne's queries would look using FOR XML instead?

    My answers:

    1. FOR XML is definitely better to use, as the article demonstrates. I had only used the manual method to build an XML string so that I wouldn't have to read the help for how to use FOR XML for all of the optional parameters. Also, note that if using SQL 2000, I don't think that all of the parameters in the FOR XML can be used, so to get the string in that format I believe that you have to build it manually.

    2. In the article, the queries that build the strings for making a comma-delimited list, or the XML string for the 30,000+ items DO use the FOR XML clause. Look at those for the example.

    If you still have an unanswered question, can you please restate it so that it can be addressed?

    Thanks,

    Wayne

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • nvanesch (8/21/2008)


    I really good performance improvement on string concatenation. I guess these problems weren't present in SQL 2000 so much as we didn't have 2GB varchar variables to work with and had to find other solutions 🙁

    However there are still merits in looking into other options of rowset manipulation as string concatenation and csv strings should really be a last resort and not part of the main toolset as we've had programmers utilise csv strings to have their code break when commas are included in the data.

    As a note I think it should be stated that this code is for SQL 2005 and up.

    Thanks for a good article.

    I agree with you about being careful when using csv strings. However, sometimes you still have to. For instance, if you are trying to pass a reporting services multi-valued parameter to a stored procedure, you need to do so either by converting it into XML (see article http://www.sqlservercentral.com/articles/Reporting+Services/62731/ ), or by a delimited string. A delimited string should use as the delimiter something that wouldn't appear in the data, such as char(255). What I've done in the article can easily be modified to handle whatever delimiter you desire to use. I just used csv as an example that all would easily understand.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • lucian (8/21/2008)


    Very useful.

    I use it to process a comma delimited list of keys or any other list of values that is passed as a parameter to a stored proc.

    Now I do not have to worry about the string being too long, Mine will never be as big as 31k list of values/keys.

    My function to split the string up and return a table(that can be used in joins etc) now looks like this :

    :angry: See attachment cos I cannot get xml tags to display correctly :blush:

    If you change ItemVal to StrVal and remove the insert into the temp table, you should be able to just return the data directly from the select clause. This would avoid having to go through an intermediate temp table.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • WayneS (8/21/2008)


    anil_mootha (8/20/2008)


    Hi Paul,

    It looks like nobody's bothered to answer our question. To me till I don't get a satisfactory answer, this topic doesn't make any sense!

    Have a wonderful day!

    I thought I had answered the question... I'm sorry if I haven't.

    I had seen two questions:

    1. Why would anyone build an XML string manually instead of using FOR XML?

    2. Could anyone show us how one of Wayne's queries would look using FOR XML instead?

    My answers:

    1. FOR XML is definitely better to use, as the article demonstrates. I had only used the manual method to build an XML string so that I wouldn't have to read the help for how to use FOR XML for all of the optional parameters. Also, note that if using SQL 2000, I don't think that all of the parameters in the FOR XML can be used, so to get the string in that format I believe that you have to build it manually.

    2. In the article, the queries that build the strings for making a comma-delimited list, or the XML string for the 30,000+ items DO use the FOR XML clause. Look at those for the example.

    If you still have an unanswered question, can you please restate it so that it can be addressed?

    Thanks,

    Wayne

    I am so much of a noob at using XML in queries that I did not recognize the FOR XML in the statements you posted. :blush: Sorry about that. Glad for your explanations. 🙂

    Paul DB

  • WayneS (8/21/2008)


    lucian (8/21/2008)


    Very useful.

    I use it to process a comma delimited list of keys or any other list of values that is passed as a parameter to a stored proc.

    Now I do not have to worry about the string being too long, Mine will never be as big as 31k list of values/keys.

    My function to split the string up and return a table(that can be used in joins etc) now looks like this :

    :angry: See attachment cos I cannot get xml tags to display correctly :blush:

    If you change ItemVal to StrVal and remove the insert into the temp table, you should be able to just return the data directly from the select clause. This would avoid having to go through an intermediate temp table.

    But I want to return a table that I can use in a inner join with other tables, or in my where clause.

    Ex

    --somewhere in my sp

    select * from tblCustomer C inner join dbo.delimstrtoTable(@SelctedCustIdList) T on C.Customerid = T.StrVal

    or alt.

    select * from tblCustomer where CustomerId in(select strval from dbo.DelimStrToTable(@SelctedCustIdList))

  • Hi,

    Here is a simple example of how I implement XML in sql2005:

    declare @XmlList XML

    Declare @Property varchar(50)

    Declare @ObjectName varchar(50)

    Declare @ColumnName varchar(50)

    Declare @Value varchar(50)

    Declare @rowcount int

    Declare @count int

    Declare @XmlCount int

    set @Count=1

    select @XmlList=

    '

    '

    select @XmlCount=CONVERT(varchar,@XmlList.query('count(//Rows/Properties/.)'))

    set @rowcount=CONVERT(int,@XmlCount)

    WHILE(@count<=@rowcount)

    BEGIN

    Select

    @ObjectName=data.value('@ObjectName[1]','varchar(50)'),

    @ColumnName=data.value('@ColumnName[1]','varchar(50)'),

    @Property=data.value('@Property[1]','varchar(50)'),

    @Value=data.value('@Value[1]','varchar(50)')

    from @XmlList.nodes('//Rows/Properties[position()=sql:variable("@count")]') XmlList(data)

    set @count=@count+1

    select @ObjectName as Object

    select @ColumnName as [Column]

    select @Property as [Property]

    Select @Value as [Value]

    END

    select @rowcount as count

    I usually pass the XML object @XmlList as parameter from app that builds up the XML string. In this case .NET.

    Hope this helps,

    Argneka

    PS: I have not tested on huge data sets, but it works very well for what I use it for.

  • Hi,

    The XML string is not properly displaying on my previous post. If you would like full example, just send me an email.

    Thanks,

    Argneka

  • lucian (8/21/2008)


    But I want to return a table that I can use in a inner join with other tables, or in my where clause.

    I understand. I mistakenly thought that you could just return the output from the select statement directly without having to go through the @table variable. (For a very large delimited string, this may cause additional disk activity.)

    Wayne

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Just to answer the rhetorical questions about building xml by wrapping tag constants around data, rather than use a "For XML" statement, we should remember that a lot of code was generated before SQL Server had native XML support. Code that isn't broken isn't revisted every time a new capability is introduced. That said, I just had to test out "For XML" against simple string concatenation to see the performance difference. I was stunned by the difference in CPU efficiency.

    Using the following:

    select @CSV = (select ',' + cast(n as varchar(6)) from dbo.Tally where N <=20000 FOR XML PATH('') )

    CPU Time was 20 seconds

    Using the "traditional"

    set @csv = ''

    select @CSV = @csv+',' + cast(n as varchar(6))

    from dbo.Tally where N <=20000

    CPU Time was 1219 seconds.

    set @csv = ''

    select @CSV = ',' + cast(n as varchar(6)) +@css

    from dbo.Tally where N <=20000

    CPU time was 580 MS, but the numbers are backwards

    I'm sold. A couple of our core functions used to generate parsed strings are about to be modified. Good article. Good to know.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • bhovious (8/21/2008)


    Just to answer the rhetorical questions about building xml by wrapping tag constants around data, rather than use a "For XML" statement, we should remember that a lot of code was generated before SQL Server had native XML support. Code that isn't broken isn't revisted every time a new capability is introduced.

    Excellent point.

    That said, I just had to test out "For XML" against simple string concatenation to see the performance difference. I was stunned by the difference in CPU efficiency.

    Using the following:

    select @CSV = (select ',' + cast(n as varchar(6)) from dbo.Tally where N <=20000 FOR XML PATH('') )

    CPU Time was 20 seconds

    Using the "traditional"

    set @csv = ''

    select @CSV = @csv+',' + cast(n as varchar(6))

    from dbo.Tally where N <=20000

    CPU Time was 1219 seconds.

    A whole 20 minutes faster! Fantastic! (well, 1 second short of 20 minutes...)

    set @csv = ''

    select @CSV = ',' + cast(n as varchar(6)) +@css

    from dbo.Tally where N <=20000

    CPU time was 580 MS, but the numbers are backwards

    I assume the @css is @csv...

    Is your cpu time so short the second time around because you had just cached it from the first time? 20,000 numbers aren't going to take up a lot of memory.

    I'm sold. A couple of our core functions used to generate parsed strings are about to be modified. Good article. Good to know.

    I'm glad it helped someone out.

    Wayne

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 15 posts - 16 through 30 (of 39 total)

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