MS SQL XML - XQuery modify really slow

Author

XQuery modify really slow

Craig Jones

2005-10-27, 9:25 am

Hi,

Is it just my setup or is XQuery _really_ slow when using the modify
statement?

We have a product that uses XML throughout and with SQL2000 we shred the xml
document (via a transform) for storage and use FOR XML (with a transform) to
reassemble them again. I've been able to achieve fantastic results using
x.query (which saves us huge amounts of time in transforms), but struggle to
modify a value or insert an element with x.modify in less than a second. This
important as product is an enterprise application with lots of updates.

I have tried different combinations of indexes, but if anything, it makes it
go slower... The SQL 2005 Sept CTP was installed on a fresh Virtual PC with
Windows 2003 SP1, with the default settings and a new database. The xml
document was just under a meg in size.

I've got a schema for the xml document, but have yet to load it into the
database.

I would appreciate any advice and can supply the sample xml document if it
helps...

Regards,

Craig.

This is an example of a SQL script.

--- Create the table
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].& #91;ProcessDefinitio
nXML]') AND type in (N'U'))
DROP TABLE [dbo].& #91;ProcessDefinitio
nXML]
GO
CREATE TABLE [dbo].& #91;ProcessDefinitio
nXML](
& #91;ProcessDefinitio
nID] [int] NOT NULL IDENTITY(1,1) PRIMARY KEY,
[XMLdoc] [xml] NOT NULL
) ON [PRIMARY]
GO

-- Import an existing process definition document
INSERT INTO ProcessDefinitionXML
([XMLdoc] )
SELECT * FROM OPENROWSET(BULK N'c:\output5.xml', SINGLE_BLOB) AS XMLdoc;
GO

-- create the primary XML index
CREATE PRIMARY XML INDEX ProcessDefinitionXML
_XMLdoc ON
ProcessDefinitionXML
(XMLdoc)
GO

--- use one or more of the following indexes.
CREATE XML INDEX ProcessDefinitionXML
_xPATH ON ProcessDefinitionXML
(XMLdoc)
USING XML INDEX ProcessDefinitionXML
_XMLdoc
FOR PATH;
GO
CREATE XML INDEX ProcessDefinitionXML
_xVALUE ON ProcessDefinitionXML
(XMLdoc)
USING XML INDEX ProcessDefinitionXML
_XMLdoc
FOR VALUE;
GO
CREATE XML INDEX ProcessDefinitionXML
_xPROPERTY ON
ProcessDefinitionXML
(XMLdoc)
USING XML INDEX ProcessDefinitionXML
_XMLdoc
FOR PROPERTY;
GO

--- Perform a _really_ fast query. Less than 20ms to come back.
SELECT
XMLdoc.query('/FLOSUITE/ProcessDefinition[@ID="5"]/WorkflowDefinition[@ID="6"]/Activities/Task[@ID="19"]')
FROM ProcessDefinitionXML

WHERE & #91;ProcessDefinitio
nID] = 1

--- Perform a _really_ slow modification. More than 1200ms to come back.
--- The Execution plan said all of the time was spent
--- spooling and sorting for the Index_Update (xPATH, xVALUE, xPROPERTY)
with zero actual rows.
UPDATE ProcessDefinitionXML

SET XMLdoc.modify('
replace value of
(/FLOSUITE/ProcessDefinition[@ID="5"]/WorkflowDefinition[@ID="6"]/Activities/Task[@ID="19"]/@Flags)[1] with "42"')
WHERE & #91;ProcessDefinitio
nID] = 1

Michael Rys [MSFT]

2005-10-27, 9:25 am

Have you checked how much data you update and how many indices do you have
that need to be maintained?

Also, how have you distributed your log files and datafiles on the disks?

Best regards
Michael

"Craig Jones" < CraigJones@discussio
ns.microsoft.com> wrote in message
news:77C60436-5BE2-4FD6-8702- 73035F3D3DA7@microso
ft.com...
> Hi,
>
> Is it just my setup or is XQuery _really_ slow when using the modify
> statement?
>
> We have a product that uses XML throughout and with SQL2000 we shred the
> xml
> document (via a transform) for storage and use FOR XML (with a transform)
> to
> reassemble them again. I've been able to achieve fantastic results using
> x.query (which saves us huge amounts of time in transforms), but struggle
> to
> modify a value or insert an element with x.modify in less than a second.
> This
> important as product is an enterprise application with lots of updates.
>
> I have tried different combinations of indexes, but if anything, it makes
> it
> go slower... The SQL 2005 Sept CTP was installed on a fresh Virtual PC
> with
> Windows 2003 SP1, with the default settings and a new database. The xml
> document was just under a meg in size.
>
> I've got a schema for the xml document, but have yet to load it into the
> database.
>
> I would appreciate any advice and can supply the sample xml document if it
> helps...
>
> Regards,
>
> Craig.
>
>
>
>
> This is an example of a SQL script.
>
>
> --- Create the table
> IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
> OBJECT_ID(N'[dbo].& #91;ProcessDefinitio
nXML]') AND type in (N'U'))
> DROP TABLE [dbo].& #91;ProcessDefinitio
nXML]
> GO
> CREATE TABLE [dbo].& #91;ProcessDefinitio
nXML](
> & #91;ProcessDefinitio
nID] [int] NOT NULL IDENTITY(1,1) PRIMARY KEY,
> [XMLdoc] [xml] NOT NULL
> ) ON [PRIMARY]
> GO
>
> -- Import an existing process definition document
> INSERT INTO ProcessDefinitionXML
([XMLdoc] )
> SELECT * FROM OPENROWSET(BULK N'c:\output5.xml', SINGLE_BLOB) AS
> XMLdoc;
> GO
>
> -- create the primary XML index
> CREATE PRIMARY XML INDEX ProcessDefinitionXML
_XMLdoc ON
> ProcessDefinitionXML
(XMLdoc)
> GO
>
> --- use one or more of the following indexes.
> CREATE XML INDEX ProcessDefinitionXML
_xPATH ON
> ProcessDefinitionXML
(XMLdoc)
> USING XML INDEX ProcessDefinitionXML
_XMLdoc
> FOR PATH;
> GO
> CREATE XML INDEX ProcessDefinitionXML
_xVALUE ON
> ProcessDefinitionXML
(XMLdoc)
> USING XML INDEX ProcessDefinitionXML
_XMLdoc
> FOR VALUE;
> GO
> CREATE XML INDEX ProcessDefinitionXML
_xPROPERTY ON
> ProcessDefinitionXML
(XMLdoc)
> USING XML INDEX ProcessDefinitionXML
_XMLdoc
> FOR PROPERTY;
> GO
>
>
> --- Perform a _really_ fast query. Less than 20ms to come back.
> SELECT
> XMLdoc.query('/FLOSUITE/ProcessDefinition[@ID="5"]/WorkflowDefinition[@ID="6"]/Activities/Task[@ID="19"]')
> FROM ProcessDefinitionXML

> WHERE & #91;ProcessDefinitio
nID] = 1
>
>
> --- Perform a _really_ slow modification. More than 1200ms to come back.
> --- The Execution plan said all of the time was spent
> --- spooling and sorting for the Index_Update (xPATH, xVALUE, xPROPERTY)
> with zero actual rows.
> UPDATE ProcessDefinitionXML

> SET XMLdoc.modify('
> replace value of
> (/FLOSUITE/ProcessDefinition[@ID="5"]/WorkflowDefinition[@ID="6"]/Activities/Task[@ID="19"]/@Flags)[1]
> with "42"')
> WHERE & #91;ProcessDefinitio
nID] = 1
>
>

Kent Tegels

2005-10-27, 9:25 am

Hello Craig,

Adding to what Michael had to say, doing XML DML is slower by its nature.
The usual processing of a DML operation isn't easily effected on the internal,
binary form that the instances are stored in. It used to be that the instance
was deserialized and operations were performed against the whole instance
which then had to be immediately reserialized and committed to keep the data
in a consistent state. I think the worked around of some that pain, but obviously
not all of it.

The other thing is to keep in mind is that the effects of having XML indexes
on a column and amplified compared to the effects of having an index on a
simple scalar value column set. Not only does the DML operation have to modify
the instance, it has update a more complex index.

In looking at your example, I think you might also be over indexing and that
can only make this problem worse. Try pulling of the secondary indexes and
see if materially affects your performance. If you want to eek out a bit
query at the expense of update perf, and if the majority of your queries
are like those in the example, you probably just need the value index.

So in short, I don't like using the XML datatype in essentially OLTP situations.
My first rule is shred to normalized form where you can and you'll probably
get the best overall performance. This case is one of the reasons why I say
that. But then, Michael says I'm too conservative. :)

Thanks!

Kent Tegels
DevelopMentor
Blogging @ http://staff.develop.com/ktegels/

Eugene Kogan [MSFT]

2005-10-27, 9:25 am

I expect that removing secondary XML indexes won't help performance of
replacing value much.
If update performance is important you may consider splitting larger XML
instances into multiple smaller ones and updating smaller XML instances and
fewer of them. You'll have to reconstruct original XML instances with FOR
XML then.
Or, as Kent suggested, for performing heavy updates it may be the best to
shred XML and store it in a more normalized form.

Best regards,
Eugene
---
This posting is provided "AS IS" with no warranties, and confers no rights.

"Craig Jones" < CraigJones@discussio
ns.microsoft.com> wrote in message
news:77C60436-5BE2-4FD6-8702- 73035F3D3DA7@microso
ft.com...
> Hi,
>
> Is it just my setup or is XQuery _really_ slow when using the modify
> statement?
>
> We have a product that uses XML throughout and with SQL2000 we shred the
> xml
> document (via a transform) for storage and use FOR XML (with a transform)
> to
> reassemble them again. I've been able to achieve fantastic results using
> x.query (which saves us huge amounts of time in transforms), but struggle
> to
> modify a value or insert an element with x.modify in less than a second.
> This
> important as product is an enterprise application with lots of updates.
>
> I have tried different combinations of indexes, but if anything, it makes
> it
> go slower... The SQL 2005 Sept CTP was installed on a fresh Virtual PC
> with
> Windows 2003 SP1, with the default settings and a new database. The xml
> document was just under a meg in size.
>
> I've got a schema for the xml document, but have yet to load it into the
> database.
>
> I would appreciate any advice and can supply the sample xml document if it
> helps...
>
> Regards,
>
> Craig.
>
>
>
>
> This is an example of a SQL script.
>
>
> --- Create the table
> IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
> OBJECT_ID(N'[dbo].& #91;ProcessDefinitio
nXML]') AND type in (N'U'))
> DROP TABLE [dbo].& #91;ProcessDefinitio
nXML]
> GO
> CREATE TABLE [dbo].& #91;ProcessDefinitio
nXML](
> & #91;ProcessDefinitio
nID] [int] NOT NULL IDENTITY(1,1) PRIMARY KEY,
> [XMLdoc] [xml] NOT NULL
> ) ON [PRIMARY]
> GO
>
> -- Import an existing process definition document
> INSERT INTO ProcessDefinitionXML
([XMLdoc] )
> SELECT * FROM OPENROWSET(BULK N'c:\output5.xml', SINGLE_BLOB) AS
> XMLdoc;
> GO
>
> -- create the primary XML index
> CREATE PRIMARY XML INDEX ProcessDefinitionXML
_XMLdoc ON
> ProcessDefinitionXML
(XMLdoc)
> GO
>
> --- use one or more of the following indexes.
> CREATE XML INDEX ProcessDefinitionXML
_xPATH ON
> ProcessDefinitionXML
(XMLdoc)
> USING XML INDEX ProcessDefinitionXML
_XMLdoc
> FOR PATH;
> GO
> CREATE XML INDEX ProcessDefinitionXML
_xVALUE ON
> ProcessDefinitionXML
(XMLdoc)
> USING XML INDEX ProcessDefinitionXML
_XMLdoc
> FOR VALUE;
> GO
> CREATE XML INDEX ProcessDefinitionXML
_xPROPERTY ON
> ProcessDefinitionXML
(XMLdoc)
> USING XML INDEX ProcessDefinitionXML
_XMLdoc
> FOR PROPERTY;
> GO
>
>
> --- Perform a _really_ fast query. Less than 20ms to come back.
> SELECT
> XMLdoc.query('/FLOSUITE/ProcessDefinition[@ID="5"]/WorkflowDefinition[@ID="6"]/Activities/Task[@ID="19"]')
> FROM ProcessDefinitionXML

> WHERE & #91;ProcessDefinitio
nID] = 1
>
>
> --- Perform a _really_ slow modification. More than 1200ms to come back.
> --- The Execution plan said all of the time was spent
> --- spooling and sorting for the Index_Update (xPATH, xVALUE, xPROPERTY)
> with zero actual rows.
> UPDATE ProcessDefinitionXML

> SET XMLdoc.modify('
> replace value of
> (/FLOSUITE/ProcessDefinition[@ID="5"]/WorkflowDefinition[@ID="6"]/Activities/Task[@ID="19"]/@Flags)[1]
> with "42"')
> WHERE & #91;ProcessDefinitio
nID] = 1
>
>

Joe

2005-10-27, 9:26 am

My understanding was that applying the schema to the xml field would
definitely improve performance. I'd suggest you run tests with the
schema applied, and with only a primary xml index on the xml field.
That said, I doubt that it would be as significant of an improvement as
you seem to be looking for. Also, keep in mind the overhead of the
indexes on insert processing if that is how the large xml gets into the
database.

Joe

Sponsored Links