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