Optimising XML

Question

Post reply

Optimising XML

Gail Shaw

SSC Guru

Points: 1004504
More actions
October 4, 2005 at 3:51 am

#95365

Hi all.
During some code optimisation I found a bit of XML manipulation that, according to the query plan, takes over 50% of a 7 sec query.
I'm not particuarly experienced with XML, so I'm not sure if it cam be improved and if so, how.
Anyone with XML experience that's willing to lend a hand?
CREATE TABLE #ParameterFilter (localname varchar(1000) PRIMARY KEY,Value varchar(1000))
DECLARE @xml VARCHAR(2000)
SET @xml='<FilterString><Sender /><OurReference /><Reference>PT21   </Reference><Direction /><Type /><Host /></FilterString>'
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @ParameterFilter
DECLARE @TempXML TABLE (
ID int PRIMARY KEY,
ParentID int,
NodeType int,
LocalName VARCHAR(1000),
Value ntext
)
INSERT INTO @TempXML
SELECT [id],ParentId,NodeType, localname, text as Value
    FROM OPENXML (@idoc, '')
EXEC sp_xml_removedocument @idoc
INSERT INTO #ParameterFilter
SELECT ColumnName.localname, CAST(ColumnValue.[Value] AS VARCHAR(1000))
FROM   @TempXML DataRow INNER JOIN
            @TempXML ColumnName ON DataRow.Id = ColumnName.Parentid INNER JOIN
            @TempXML ColumnValue ON ColumnName.Id = ColumnValue.Parentid
WHERE (DataRow.localname = 'FilterString')
Thanks in advance.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Viewing 10 posts - 1 through 9 (of 9 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

My first question would be why are you using xml and what are you trying to do exactly?

My second question is can you drop the xml part of the equation??

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Can't drop the xml, short of rewriting the entire front end app (which isn't an option) and large portions of the back-end code

Basically (and very simplified), the front-end app calls a generic stored proc to return data. The stored proc takes as parameters the name of the table/view and an xml string which acts as a filter. The XML is read and converted to a where clause. The example I posted would equate to

WHERE Reference='PT21'

I'm mainly wondering (since I've worked very little with XML) if there's an faster way to convert the xml into a list of 'parameters' and values (which is what the Parameterfilter table is)

Thanks

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

noeld SSC Guru Points: 96590 More actions · Answer 3

if there's an faster way to convert the xml into a list of 'parameters' and values (which is what the Parameterfilter table is)

Yes there is but you need to be more specific with the description of the the XML data that your example uses. Or at least I don't understand the logic for that "where clause generation".

You should be able to insert directly into the parameters table using the right xpath!

* Noel

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

I posted an example:

Another example:

It's the way the front end passes an array of parameters to SQL, so the only thing that's constant is the outer node (Filterstring) The subnodes depend on the table been queried and where in the front end the query is coming from.

Got a nasty feeling that I'm stuck doing it this way until SQL 2005

btw, xpath in SQL 2000?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

noeld SSC Guru Points: 96590 More actions · Answer 5

Yes XPath in SQL 2000

FROM BOL:

OPENXML(idoc int [in],rowpattern nvarchar[in],[flags byte[in]])

[WITH (SchemaDeclaration | TableName)]

Arguments

idoc

Is the document handle of the internal representation of an XML document. The internal representation of an XML document is created by calling sp_xml_preparedocument.

rowpattern

Is the XPath pattern used to identify the nodes (in the XML document whose handle is passed in the idoc parameter) to be processed as rows.

flags

Indicates the mapping that should be used between the XML data and the relational rowset, and how the spill-over column should be filled. flags is an optional input parameter, and can be one of these values.

Because it is dynamic they simply used the edge table instead!

It would have being more intuitive to use TableName columnName and Columnvalues as ELEMENTS on the XML to have a "Fixed" pattern

Cheers,

* Noel

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Thanks, that's good to know. This is the first time I'm working with xml in SQL. Haven't touched xml since I was doing webdev some years back.

Agreed, name and value elements in the xml would have been much cleaner. I'll chat with the front-end developers, see if anything can be done.

Otherwise, rollon SQL 2005.

Thanks.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jonathan Tomkins Ten Centuries Points: 1268 More actions · Answer 7

I think you can simplify the query a bit too using xpath in the WITH clause...

set nocount on

DECLARE @xml VARCHAR(2000)

DECLARE @ParameterFilter TABLE(localname varchar(1000), value varchar(1000))

DECLARE @idoc int

SET @xml='<FilterString><Sender /><OurReference /><Reference>PT21 </Reference><Direction /><Type /><Host /></FilterString>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @xml

INSERT INTO @ParameterFilter

SELECT localname, value

FROM OPENXML (@idoc, '/FilterString/node()')

WITH ( localname varchar(1000) '@mp:localname',

value varchar(1000) 'text()'

)

WHERE value is not NULL

EXEC sp_xml_removedocument @idoc

select * from @ParameterFilter

set nocount off

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

That works great. Thank you very much.

Would you mind explaining what/how the xpath works there?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jonathan Tomkins Ten Centuries Points: 1268 More actions · Answer 9

bol description of OPENXML:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_5c89.asp

OPENXML (@idoc, '/FilterString/node()')

produces a table from the doc, using the xpath provided - this just says that each row is a match for any node under FilterString under root, i.e. the children of FilterString.

the WITH clause just gives names to the fields - @mp:localname is described in the bol ref - here it just gives the name of each node we match, and text() is just the text of that node.

Hope that helps

Jon