XML parsing slowly

I'm currently trying to tune a somewhat problematic stored procedure and I've noticed that majority of cost associated with running it comes from parsing parsing some XML into a temp table. Here is the SQL in question:

CREATE TABLE #ChangeSet 
(
    RemarkTypeID TINYINT NOT NULL PRIMARY KEY,
    RemarkText VARCHAR(2500) NOT NULL,
    ListingID INT NOT NULL
)
INSERT INTO #ChangeSet
    (RemarkTypeID, 
    RemarkText,
    ListingID)
SELECT
    T.c.value('@RemarkTypeID[1]','tinyint') AS RemarkTypeID,
    T.c.value('@RemarkText[1]','varchar(2500)') AS RemarkText,
    @ListingID
FROM @RemarksXml.nodes('/Remarks[1]/Remark') AS T(c)

Also, so you have some idea of the structure of the XML being parsed:

Is there anything I can do to improve the performance of parsing these XML nodes and setting up this temp table?

There are two main things you can do to improve xml parsing performance:

• Make the xml variable/column typed, which means declaring an xsd schema on it. This will force a validation of the xml, which in and of itself will take a little time, but parsing speed will improve.

• Index an xml column (does not apply to xml variables). You can index xml columns in a number of different ways, depending on your requirements. This may give you excellent performance gains for more complex queries and lookups in xml blobs.

• If your query is part of a much larger query, I seem to remember that xml operations don't parallelize, so you might do wisely to break up a large query and do the xml parsing in a separate query from the rest of the work.

StackExchange Database Administrators Q#133902, answer score: 5