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.
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.
Subject: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>
.....
.....