Insert from XML are too slow

Question

Post reply

Insert from XML are too slow

Shogun

Valued Member

Points: 50
More actions
March 18, 2009 at 8:55 am

#127171

Hi everybody, I'm trying to insert the resultset from a XML variable and is too slow. If I just do the select part it works just fine. Wired???
Script 1
declare @cXML xml
select @cXML = (select * from AdventureWorks.Purchasing.PurchaseOrderHeader
for xml raw('item'), type, elements, root('root'))
select
x.item.value('PurchaseOrderID[1]','nvarchar(256)') as PurchaseOrderID,
x.item.value('RevisionNumber[1]','tinyint') as RevisionNumber,
x.item.value('Status[1]','tinyint') as Status,
x.item.value('OrderDate[1]','datetime') as OrderDate
from @cXML.nodes('//root/item') as x(item)
Work just fine.
--------------------------------------------------------------------------------------
Script 2
declare @cXML xml
select @cXML = (select * from AdventureWorks.Purchasing.PurchaseOrderHeader
for xml raw('item'), type, elements, root('root'))
select
x.item.value('PurchaseOrderID[1]','nvarchar(256)') as PurchaseOrderID,
x.item.value('RevisionNumber[1]','tinyint') as RevisionNumber,
x.item.value('Status[1]','tinyint') as Status,
x.item.value('OrderDate[1]','datetime') as OrderDate
into #tmpPOH
from @cXML.nodes('//root/item') as x(item)
just died!!! (in time)
I also try a table variable, is the same
Is someone have an answer I will really appreciate .
Thanks
Mariano

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

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

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Hi

Could you explain "just died"?

I tried your example and id took 810 milliseconds on my workstation for 4012 rows. It's a quiet good pc but it's no server.

Greets

Flo

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Do you have an open transaction?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Shogun Valued Member Points: 50 More actions · Answer 3

Shogun

Valued Member

Points: 50

March 19, 2009 at 5:33 am

#962588

Hi,

No, I have not any transaction

Shogun Valued Member Points: 50 More actions · Answer 4

Shogun

Valued Member

Points: 50

March 19, 2009 at 5:44 am

#962595

Did You try the Script 2?

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 5

Hi

Here the script I just tested:

IF (OBJECT_ID('tempdb..#tmpPOH') IS NOT NULL)

DROP TABLE #tmpPOH

declare @cXML xml

select @cXML = (select * from AdventureWorks.Purchasing.PurchaseOrderHeader

for xml raw('item'), type, elements, root('root'))

DECLARE @now DATETIME

SET @now = GETDATE()

select

x.item.value('PurchaseOrderID[1]','nvarchar(256)') as PurchaseOrderID,

x.item.value('RevisionNumber[1]','tinyint') as RevisionNumber,

x.item.value('Status[1]','tinyint') as Status,

x.item.value('OrderDate[1]','datetime') as OrderDate

into #tmpPOH

from @cXML.nodes('//root/item') as x(item)

PRINT ('Duration: ' + CONVERT(VARCHAR(10), DATEDIFF(MILLISECOND, @now, GETDATE())) + ' milliseconds')

Output:

(4012 row(s) affected)

Duration: 1140 milliseconds

... well it's greater than one second now but it still seems to be okay in my opinion.

Greets

Flo

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 6

I also tried script # 2 and it worked with no problem at all. While you run it, can you check the DMV sys.os_waiting_tasks and see if you can see what is your process waiting for?

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

{oops: posted in wrong form. My post makes no sense here, so I am wiping it...}

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Shogun Valued Member Points: 50 More actions · Answer 8

Thanks to everyone, the solution came from Microsoft =P

I forgot to tell you that my workstation have SP2 (now SP3 9.0.4035)

After install the SP3 the problem are gone =)

Thanks again to all of you.