XML Editor
Sign up for a WebBoard account Sign Up Keyword Search Search More Options... Options
Chat Rooms Chat Help Help News News Log in to WebBoard Log in Not Logged in
Show tree view Topic
Go to previous topicPrev TopicGo to next topicNext Topic
Postnext
dulmini sSubject: Improve xQuery Performance
Author: dulmini s
Date: 13 Feb 2013 02:41 AM
I use stylus studio (with DATA DIRECT)/xQuery to join two xmls files to one( original files are converted from CSV to xml first and then joined). Customer(header) data read first and then matching transaction data for each cusomer read. (using a loop).
Transaction data is also grouped by transaction date(year). This is done by using distinct function. Transaction data file is quite large and it is quite slow to process.( tables about 10 hrs for 20,000 customer records) I'm looking at ways to improve performance. any help or advice is much approciated. below is the sample of the code used.


(:options:)
declare option ddtek:xml-streaming "yes";
declare option ddtek:serialize "encoding=UTF-8,
omit-xml-declaration=no, indent=yes";

(:external variables:)
declare variable $esa-h-converter as xs:string external;
declare variable $esa-t-converter as xs:string external;
declare variable $input-h-data-path as xs:string external;
declare variable $input-t-data-path as xs:string external;


(:main:)
<ROOT_NODE>
{
let $heads := doc(fn:concat(
"converter:",
$esa-h-converter,
"?file:///",
$input-h-data-path))
/DATA_ROOT/CUSTOMER

for $record at $primaryKey in $heads

return
<TEMPLATE>
<CUSTOMER>
<RECORD_COUNT>{ $primaryKey }</RECORD_COUNT>
<HD_REGION>{ $record/HD_REGION/text() }</HD_REGION>
<HD_CONT>{ $record/HD_CONT/text() }</HD_CONT>
<HD_FRDATE>{ $record/HD_FRDATE/text() }</HD_FRDATE>
<HD_TODATE>{ $record/HD_TODATE/text() }</HD_TODATE>
<HD_CUSNAME>{ $record/HD_CUSNAME/text() }</HD_CUSNAME>
<HD_ADDR1>{ $record/HD_ADDR1/text() }</HD_ADDR1>
<HD_ADDR2>{ $record/HD_ADDR2/text() }</HD_ADDR2>
<HD_ADDR3>{ $record/HD_ADDR3/text() }</HD_ADDR3>
<HD_ADDR4>{ $record/HD_ADDR4/text() }</HD_ADDR4>
<HD_STATE>{ $record/HD_STATE/text() }</HD_STATE>
<HD_SUBTOWN>{ $record/HD_SUBTOWN/text() }</HD_SUBTOWN>
<HD_PCODE>{ $record/HD_PCODE/text() }</HD_PCODE>
{

let $my-transactions :=
doc(fn:concat("converter:",
$esa-t-converter,
"?file:///",
$input-t-data-path))
/DATA_ROOT
/TRANSACTION
[TR_CONT eq $record/HD_CONT
and TR_REGION eq $record/HD_REGION]
let $years := fn:distinct-values(
$my-transactions
/fn:substring(TR_DATE/text(), 1, 4))
for $period in $years
return
<PERIOD>
<RECORD_COUNT>{ $primaryKey }</RECORD_COUNT>
<YEAR>{ $period }</YEAR>
{
$my-transactions
[fn:substring(TR_DATE/text(), 1, 4) = $period]
}
</PERIOD>
}
</CUSTOMER>
</TEMPLATE>
}
</ROOT_NODE>

Postnext
Minollo I.Subject: Improve xQuery Performance
Author: Minollo I.
Date: 13 Feb 2013 11:18 AM
One thing I would try is to create the transaction document only once, and for all customers; so, basically doing:
let $all-transactions :=
doc(fn:concat("converter:",
$esa-t-converter,
"?file:///",
$input-t-data-path))

...before the external loop - so that it's done once and for all, and then you just focus on filtering $my-transactions out of it.
The other thing I would try is to change xml-streaming to "no"; with a distinct-values() in the picture, there is little value in attempting to stream; and, if anything, that could slow things down a bit.

Posttop
dulmini sSubject: Improve xQuery Performance
Author: dulmini s
Date: 18 Feb 2013 07:53 PM
Thanks for the reply.

I have merged the keys that is use to link header and transaction
and the performace improved significantly. ( takes only 5 minutes to process same file took 10 hrs before)

.....
.....
<HD_FAX>{ $record/HD_FAX/text() }</HD_FAX>
{
let $my-transactions := $transactions[concat(string(TR_CONT/text()), "_", string(TR_REGION/text())) = $header_id]
let $composed-rows := fn:distinct-values($my-transactions/fn:substring(TR_DATE/text(), 1, 4))

for $period in $composed-rows
return
<PERIOD>
.....
.....

 
Go to previous topicPrev TopicGo to next topicNext Topic
Download A Free Trial of Stylus Studio 6 XML Professional Edition Today! Powered by Stylus Studio, the world's leading XML IDE for XML, XSLT, XQuery, XML Schema, DTD, XPath, WSDL, XHTML, SQL/XML, and XML Mapping!  
go

Log In Options

Site Map | Privacy Policy | Terms of Use | Trademarks
Stylus Scoop XML Newsletter:
W3C Member
Stylus Studio® and DataDirect XQuery ™are from DataDirect Technologies, is a registered trademark of Progress Software Corporation, in the U.S. and other countries. © 2004-2016 All Rights Reserved.