|
|
 next
|
  Subject: 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>
|
|
|
|
|
top