XML : Can You Optimize XML Operations in SQL Server?

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?

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")');    

SIDE NOTE:
The XML gets generated and sent out, so it doesn't get saved into a table (or anything)

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>    

No comments:

Post a Comment