Using xml param type very slow

Question

Post reply

Using xml param type very slow

cgreathouse

SSC Eights!

Points: 855
More actions
May 20, 2010 at 8:07 am

#221274

Hello All
I have a stored procedure that takes an xml type as one of the parameters. I'm using the xml type as a way to pass in a table (using SQL Server 2005). It's a fairly complex SP. There are about 7 queries and some of them are working with decent size tables (3 million+) and doing a number of aggregates.
When I look at the execution plan there is one query that is showing to take 100% of the time relative to the other queries. Here's the query...
insert @cartItemsTable
select Items.Item.value('./@CartItemIndex','smallint'),
Items.Item.value('./@ItemID','varchar(50)'),
Items.Item.value('./@CategorySKU','int'),
Items.Item.value('./@ItemPrice','decimal(9,2)'),
Items.Item.value('./@ItemQty','smallint')
from @CartItems.nodes('/Items/Item') Items(Item)
Here's what the xml looks like for @CartItems...
<Items>
<Item CartItemIndex="0" ItemID="Some Item 1" CategorySKU="20010" ItemPrice="19.95" ItemQty="2"/>
<Item CartItemIndex="1" ItemID="Some Item 2" CategorySKU="20010" ItemPrice="9.95" ItemQty="5"/>
<Item CartItemIndex="2" ItemID="Some Item 3" CategorySKU="20010" ItemPrice="1" ItemQty="7"/>
</Items>
Here's how @cartItemsTable is declared...
declare @cartItemsTable table(CartItemIndex smallint,
ItemID varchar(50),
CategorySKU int,
ItemPrice decimal(9,2),
ItemQty smallint)
Why is it so slow to just put the xml data in a table? Is there a better way to pass the data in? I've attached the xml execution plan for this one query.
Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

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

Stefan_G SSCertifiable Points: 6609 More actions · Answer 1

How slow is this statement?

You say that it takes 100% of the query according to the execution plan, but how many ms is that ?

To see this exactly you could enable the SP:StmtEnd events in the SQL Profiler and look at the duration and CPU columns.

I have noticed that the %-numbers in the query plan can be very misleading.

cgreathouse SSC Eights! Points: 855 More actions · Answer 2

The entire stored procedure takes ~450 ms. When I look at the actual execution plan in SMS, there are 7 queries in the batch and this 1 query is showing to take up 100% of the batch.

Stefan_G SSCertifiable Points: 6609 More actions · Answer 3

cgreathouse (5/20/2010)
The entire stored procedure takes ~450 ms. When I look at the actual execution plan in SMS, there are 7 queries in the batch and this 1 query is showing to take up 100% of the batch.

I see.

Unfortunately this still does not guarantee that this query actually is the problem. As I said before the Query cost % in the actual plan is unreliable.

To get an accurate measurement please use SQL Profiler and look at the duration for the SP:StmtCompleted event.

If I run the following script the execution time is not measurable:

declare @cartItemsTable table(CartItemIndex smallint,

ItemID varchar(50),

CategorySKU int,

ItemPrice decimal(9,2),

ItemQty smallint)

DECLARE @CartItems XML = '

<Items>

<Item CartItemIndex="0" ItemID="Some Item 1" CategorySKU="20010" ItemPrice="19.95" ItemQty="2"/>

<Item CartItemIndex="1" ItemID="Some Item 2" CategorySKU="20010" ItemPrice="9.95" ItemQty="5"/>

<Item CartItemIndex="2" ItemID="Some Item 3" CategorySKU="20010" ItemPrice="1" ItemQty="7"/>

</Items>

'

SET STATISTICS TIME ON

insert @cartItemsTable

select Items.Item.value('./@CartItemIndex','smallint'),

Items.Item.value('./@ItemID','varchar(50)'),

Items.Item.value('./@CategorySKU','int'),

Items.Item.value('./@ItemPrice','decimal(9,2)'),

Items.Item.value('./@ItemQty','smallint')

from @CartItems.nodes('/Items/Item') Items(Item)

SET STATISTICS TIME OFF

SELECT * FROM @cartitemsTable

--SQL Server parse and compile time:

-- CPU time = 0 ms, elapsed time = 2 ms.

-- SQL Server Execution Times:

-- CPU time = 0 ms, elapsed time = 0 ms.

-- SQL Server Execution Times:

-- CPU time = 0 ms, elapsed time = 0 ms.

-- SQL Server Execution Times:

-- CPU time = 0 ms, elapsed time = 1 ms.

So, I actually believe that it is something else that is taking your 450 ms.

/SG

LutzM SSC Guru Points: 107049 More actions · Answer 4

I second Stefan regarding not to trust the % measures from an execution plan.

Especially if the query itself doesn't take that much time (like in your case).

The main reason is that SQL Server needs to build the execution plan at some point during the query. And that time will be added to one of the queries.

You should use STATISTICS TIME as Stefan recommended but additionally you should disable ActualExecutionPlan when taking the measurement.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]