Can You Optimize XML Operations in SQL Server?

Question

I am generating & sending XML EVENTS from the database through a SQL BROKER using SQL CLR - and it works great. However, I am looking at the SQL PLAN and am a little shocked at some of the statistics. Small transformations seem to cost quite a bit of CPU TIME.

All the examples I see online optimize the TABLE the XML sits in by adding an index (etc)...but there is no table for me (I am simply generating the XML).

As such...

Q: Is there a way to "optimize" these kind of "generational" statements?

Maybe some approaches are better than others?
I have yet to see anything online about this

Thanks.

SAMPLES OF EXPENSIVE STATEMENTS:

DECLARE @CurrentId UNIQUEIDENTIFIER = (SELECT @Event.value('(/Event/@auditId)[1]', 'UNIQUEIDENTIFIER'));

SET @Event.modify('replace value of (/Event/@auditId)[1][1] with sql:variable("@NewId")');

EVENT XML:
An event would look like...

<Event auditId="FE4D0A4C-388B-E611-9B4D-0050569B733D" force="false" CreatedOn="2016-10-05T20:14:20.020">
    <DataSource machineName="ABC123">DatabaseName</DataSource>
    <Topic>
        <Filter>TOPIC/ENTITY/ACTION</Filter>
    </Topic>
    <Name>Something.Created</Name>
    <Contexts>
        <Context>
            <Name>TableName</Name>
            <Key>
                <IssueId>000</IssueId>
            </Key>
        </Context>
    </Contexts>
</Event>

You can create indices on xml columns. If the structure of your XML is something that can be predicted usefully, go for it. In any case, XML is never going to be as fast as a well designed relational structure, that's just on of the trade-offs you made when you decided to use XML :) You're parsing text. Parsing text always costs you at the very least going through every single character. When you use a xml column, this isn't an issue (the internal storage of xml in MS SQL is not text-based), but in your case, there's no way around it. — Luaan
– Luaan, Commented Oct 19, 2016 at 12:43
@Shnugo Yeah, as I noted, that's the trade-off of using XML. XML is an interchange format, not a good way to query for data or storage. Every extension you make to make it better at querying comes at a cost, and still can't quite compare to a good format designed for querying. In a typical interchange scenario, the cost isn't a big deal - you need to parse the whole thing in the typical case. The problem is that people are using XML in scenarios where you're not parsing the whole thing at once (config files - fine; data storage - are you out of your mind?). — Luaan
– Luaan, Commented Oct 19, 2016 at 13:45
@Luaan, well, in this case it seems to be kind of interface / exchange... And yes, you are right, XML is not the right place to store actively used data permanently. To your good-bad list I'd add archives and common formats (CDA, XLSX). — Gottfried Lesigang
– Gottfried Lesigang, Commented Oct 19, 2016 at 13:54

Community · Accepted Answer · 2017-04-13 12:42:39Z

An XML index will not help you with this (Read this). There are very rare situations, where this kind of index would help you. The effect is high, if you read from your XML with full-path. In the moment you are using XQuery, any kind of navigation, it makes things even worse.

.modify() is quite heavy. In this special case it could be faster to rebuild the XML as such (you know more about it than the engine does):

DECLARE @xml XML=N'
<Event auditId="FE4D0A4C-388B-E611-9B4D-0050569B733D" force="false" CreatedOn="2016-10-05T20:14:20.020">
    <DataSource machineName="ABC123">DatabaseName</DataSource>
    <Topic>
        <Filter>TOPIC/ENTITY/ACTION</Filter>
    </Topic>
    <Name>Something.Created</Name>
    <Contexts>
        <Context>
            <Name>TableName</Name>
            <Key>
                <IssueId>000</IssueId>
            </Key>
        </Context>
    </Contexts>
</Event>';

DECLARE @NewId UNIQUEIDENTIFIER=NEWID();

SELECT @NewId AS [@auditId]
      ,e.value('@force','nvarchar(max)') AS [@force] --read this as string to avoid expensive conversions
      ,e.value('@CreatedOn','nvarchar(max)') AS [@CreatedOn] --same here
      ,e.query('*') AS [node()] --read "as-is"
FROM @xml.nodes('/Event') AS A(e)
FOR XML PATH('Event');

There is - for sure! - no general approach to get XML things faster. If this existed, it would be the one and only ...

I'd monitor the system and pick out the most expensive calls and try to modify them one by one...

Collectives™ on Stack Overflow

Can You Optimize XML Operations in SQL Server?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related