|
| Hello,
I have a similair question as posed in the threads "XQuery performance
on large XL file" and "Querying XML data type performance/index" namely
what way to go to improve XQuery performance on large XML.
Scenario is as follows:
- Daily application log files are collected and stored in XML column in
SQL Server 2005. Maximum size of these files now is about 10 Mb (about
30000 log elements 's') but could get much larger in the future;
- Then XQueries run over the imported XML to create/update relational
tables based on event types (e.g. 'user.up.document.openlocation') in
the XML.
With small amount of log elements (<100) everything runs fine of
course, but with 30000 things slow down seriously (>30 minutes for
executing query)
The table with XML column has primary index and secondary (PROPERTY)
index, I'm bot sure if this is the best way to index.
I have already read some information and will read a lot more on
improving the xquery statements I'm writing but I hope some more
experienced users can point me in the right direction.
Included in this message are a eexample XML chunk and a query I'm
using.
Best regards,
Emil
XQuery sample
DECLARE @xmlvar xml
SET @xmlvar = (
SELECT [stats]
FROM [dailyreports]
)
SELECT
stats.s.value('declare namespace
x="http://www.thefirm.nl/2006/XSD/magmastatsreport.xsd";
@d', 'varchar(50)') AS reportdatetime,
stats.s.query('declare namespace
x="http://www.thefirm.nl/2006/XSD/magmastatsreport.xsd";
x:k/x:i[@t = "locationid"]') AS iets
FROM @xmlvar.nodes('declare namespace
x="http://www.thefirm.nl/2006/XSD/magmastatsreport.xsd"; /x:stats/x:s')
stats(s)
WHERE stats.s.exist('declare namespace
x="http://www.thefirm.nl/2006/XSD/magmastatsreport.xsd";
x:k/x:i[(@t = "event") and (. = "user.up.document.openlocation")]') =
1
XML sample
<?xml version="1.0" encoding="UTF-8"?>
<stats xmlns="http://www.thefirm.nl/2006/XSD/magmastatsreport.xsd">
<info reportdatetime="2006-11-15T00:00:00.000+01:00"
generateddatetime="2006-11-16T00:15:00.154+01:00"
generator="tfsreportagent" generatorversion="1.0"
applicationid="testapp"/>
<s d="2006-11-15T07:11:35.067+01:00">
<k>
<i t="module">tfupdocuments</i>
<i t="event">user.up.document.openlocation</i>
<i t="bookid">docs/nieuw</i>
<i t="locationid">docs/nieuw/26.html</i>
<i t="origin">document</i>
<i t="profileid">5A8CB963-EE90-4F4B-8568-62B6691F3CA2</i>
<i t="licenseid">abcd@abcd.nl</i>
</k>
<p t="questdocument">True</p>
<p t="title"><![CDATA[Nieuw in deze uitgave]]></p>
<p t="booktitle"><![CDATA[Wat is nieuw]]></p>
</s>
<s d="2006-11-15T07:12:20.473+01:00">
<k>
<i t="module">tfupdocuments</i>
<i t="event">user.up.document.closebook</i>
<i t="bookid">docs/nieuw</i>
<i t="licenseitemid">docs/nieuw</i>
<i t="usedlicenseitemid">docs/nieuw</i>
<i t="profileid">5A8CB963-EE90-4F4B-8568-62B6691F3CA2</i>
<i t="licenseid">abcd@abcd.nl</i>
</k>
<p t="questdocument">True</p>
<p t="timedout">False</p>
<p t="openedtime">00:00:45</p>
<p t="title"><![CDATA[Wat is nieuw]]></p>
</s>
</stats>
|
|