March 17, 2011 at 5:55 am
I wrote a piece of code that performed well on my laptop, extracting data from an XML into a temp table.
Job done!
So I thought... until I moved that code to another server to find out that what took one second on my laptop took 30mns on the server :crying:
CREATE TABLE #XmlData(XmlId INT NOT NULL PRIMARY KEY IDENTITY(1,1)
, XmlCountry NVARCHAR(100) NULL
, XmlYear NVARCHAR(100) NULL
, XmlPeriod NVARCHAR(100) NULL
, XmlCostCentre NVARCHAR(100) NULL
, XmlVersion NVARCHAR(100) NULL
, XmlAccount NVARCHAR(100) NULL
, XmlCurrency NVARCHAR(100) NULL
, XmlValue NVARCHAR(100) NULL);
DECLARE @XmlData XML
SELECT @XmlData = '<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<data>
<header>
<createdBy>Eric from file</createdBy>
<dataType>CCFINMAFACTDATA</dataType>
<mail>blablabla</mail>
<mail>blablabla</mail>
</header>
<content>
<Row>
<Country>FI</Country>
<Year>2010</Year>
<Period>4</Period>
<CostCentre>1100</CostCentre>
<Version>ACT</Version>
<Account>1242</Account>
<Currency>EUR</Currency>
<Value>376.00</Value>
</Row>
<Row>
<Country>FI</Country>
<Year>2010</Year>
<Period>4</Period>
<CostCentre>3027</CostCentre>
<Version>ACT</Version>
<Account>1242</Account>
<Currency>EUR</Currency>
<Value>1300.00</Value>
</Row>
</content>
</data>'
INSERT INTO #XmlData( XmlCountry
, XmlYear
, XmlPeriod
, XmlCostCentre
, XmlVersion
, XmlAccount
, XmlCurrency
, XmlValue
)
SELECT T.C.value('(./Country)[1]', 'NVARCHAR(100)')
,T.C.value('(./Year)[1]', 'NVARCHAR(100)')
,T.C.value('(./Period)[1]', 'NVARCHAR(100)')
,T.C.value('(./CostCentre)[1]', 'NVARCHAR(100)')
,T.C.value('(./Version)[1]', 'NVARCHAR(100)')
,T.C.value('(./Account)[1]', 'NVARCHAR(100)')
,T.C.value('(./Currency)[1]', 'NVARCHAR(100)')
,T.C.value('(./Value)[1]', 'NVARCHAR(100)')
FROM @XmlData.nodes('/data/content/Row') T(C)
My sample above has only two rows but my real test had about 4,000
Looking at the query plans between the fast and slow machines (both Sql 2005 sp2 64 bits), one stricking difference is that there are several "Table Valued Function (XML Reader)" on the fast machine replaced by "Table Valued Function (XML Reader)" followed by "Table Spool (Eager Spool)" on the slow side.
Worse, the "Table Valued Function" (TVF) on the slow side shows an estimated number of rows of 10,000 (default for "outside source"?) and an actual of ~64,000 while its equivalent on the fast side shows an estimated rows of 900 for an actual of 7,500
So, this seems to indicate that there is some different XML component (outside sql server?) on both servers.
Any idea how I can make some progress on this?
I have attached the sql script I use for testing (please rename.sql) and the two query plans I get
March 18, 2011 at 3:51 am
Although we don't understand why, a colleague has found an alternative that does not missbehave.
He simply copies the XML into a simple temp table before doing the extract from that temp table instead of the variable
So, he replaces
INSERT INTO #XmlData(bla bla)
SELECT bla bla bla
FROM @XmlData.nodes('/data/content/Row') T(C)
with
CREATE TABLE #XMLTest(XMLData xml NOT NULL)
INSERT INTO #XMLTest (XMLData) VALUES(@XmlData)
INSERT INTO #XmlData(bla bla)
SELECT bla bla bla
FROM #XMLTest
CROSS APPLY XMLData.nodes('/data/content/Row') T(C)
And that seems to cure the problem...
Weird...
but it works! 😀
March 18, 2011 at 3:56 am
The cure is because an xml specific column will index itself internally, and I don't have all the rules handy.
Can you doublecheck your laptop and the server engine you're running it against are on the same service pack?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 18, 2011 at 4:15 am
I checked that but could not find something different between the "fast" and the "slow" group of machines.
It was running fast on my laptop with either 2005 sp3 (32 bits) or 2008 R2 (32 bits) as well as on a server with 2005 sp2 (64 bits) but was running slow on a bunch of other servers all using 2005 sp2.
I looked into the versions of MSXML and it was a mixture of 6.0 and 6.0sp2 on both sides
There is something called SQLXML as well but it was either 4 on both sides or not present.
March 18, 2011 at 4:21 am
Eric Mamet (3/18/2011)
I checked that but could not find something different between the "fast" and the "slow" group of machines.It was running fast on my laptop with either 2005 sp3 (32 bits) or 2008 R2 (32 bits) as well as on a server with 2005 sp2 (64 bits) but was running slow on a bunch of other servers all using 2005 sp2.
I looked into the versions of MSXML and it was a mixture of 6.0 and 6.0sp2 on both sides
There is something called SQLXML as well but it was either 4 on both sides or not present.
*headscratch* I'm going to leave this up to folks who have messed with XQuery more then I have. That's boggling to me as well.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 18, 2011 at 4:25 am
Thanks for your help anyway.
I did not know about the self indexing.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply