Xquery performence
Thursday, 27 August 2020 · 3 messages
CN
chandra sekhar n27 Aug 2020 07:38 #
Dear Basex team,
We are facing an xquery performance issue. Please find the query in below and please suggest any clue to optimize query
let $subbrk :=
(collection("867509_Voice_DECEMBER-19_Billed_1")/SUBCUSTBRK)
let $detailUsageTxn := $subbrk/DETAIL/TRANSACTION[@Usage = 'usage']
let $DistCallTypesAll := ($detailUsageTxn/SUB_SECTION/@Type)
let $DistCallTypes := ( for $r in $DistCallTypesAll group by $ct
:= $r return $ct)*//Here taking nearly 3 minutes*
let $allmonths := distinct-values($subbrk/Month)
let $rowcnt := count($detailUsageTxn/SUB_SECTION)
let $months := $allmonths[position() le 10000]
let $opttype := 'NumVal'
let $durop := 'hh:mm:ss' return
(
if($DistCallTypes!=''
)
then(
for $month in $months
return
<R>
<GRPBY>{ $month }</GRPBY>{
if($opttype = 'NumVal') then
for $ct in $DistCallTypes
let $ctCnt :=
sum($detailUsageTxn/SUB_SECTION[data(@Type) = data($ct) and
data(../../../Month) = $month]/@Calls)
*//Here taking 4 to 5 minutes*
return element
{
replace(concat('_',data($ct)),'(\.|\[|\]|\\|\||\-|\^|\$|\?|\*|\+|\{|\}|\(|\)|
|')', '_') }
{xs:decimal($ctCnt)}
else ()
}
</R>)
else()
)Regards,
Chandrasekhar
CG
Christian Grün27 Aug 2020 08:30 #
Hi Chandra,
We’d appreciate to see a little XML document that allows us to test your query.
Best
Christian
Quoted text · chandra sekhar n · 56 lines
chandra sekhar n <vcsred at gmail.com> schrieb am Do., 27. Aug. 2020, 07:38: > Dear Basex team, > We are facing an xquery performance issue. Please find the query in below > and please suggest any clue to optimize query > > let $subbrk := > (collection("867509_Voice_DECEMBER-19_Billed_1")/SUBCUSTBRK) > let $detailUsageTxn := $subbrk/DETAIL/TRANSACTION[@Usage = 'usage'] > > let $DistCallTypesAll := ($detailUsageTxn/SUB_SECTION/@Type) > let $DistCallTypes := ( for $r in $DistCallTypesAll group by $ct > := $r return $ct) > > *//Here taking nearly 3 minutes* > > let $allmonths := distinct-values($subbrk/Month) > let $rowcnt := count($detailUsageTxn/SUB_SECTION) > let $months := $allmonths[position() le 10000] > let $opttype := 'NumVal' > let $durop := 'hh:mm:ss' return > ( > if($DistCallTypes!='' > ) > then( > for $month in $months > return > <R> > <GRPBY>{ $month }</GRPBY>{ > if($opttype = 'NumVal') then > > for $ct in $DistCallTypes > > let $ctCnt := > sum($detailUsageTxn/SUB_SECTION[data(@Type) = data($ct) and > data(../../../Month) = $month]/@Calls) > *//Here taking 4 to 5 minutes* > return element > { > replace(concat('_',data($ct)),'(\.|\[|\]|\\|\||\-|\^|\$|\?|\*|\+|\{|\}|\(|\)| > |')', '_') } > {xs:decimal($ctCnt)} > else () > } > > </R>) > else() > ) > > > > Regards, > Chandrasekhar > > > >
CG
Christian Grün27 Aug 2020 09:28 #
Thanks for the example document. – Have you already switched to the latest version of BaseX?
Quoted text · Christian Grün · 59 lines
On Thu, Aug 27, 2020 at 8:30 AM Christian Grün <christian.gruen at gmail.com> wrote: > > Hi Chandra, > > We’d appreciate to see a little XML document that allows us to test your query. > > Best > Christian > > > > chandra sekhar n <vcsred at gmail.com> schrieb am Do., 27. Aug. 2020, 07:38: >> >> Dear Basex team, >> We are facing an xquery performance issue. Please find the query in below and please suggest any clue to optimize query >> >> let $subbrk := (collection("867509_Voice_DECEMBER-19_Billed_1")/SUBCUSTBRK) >> let $detailUsageTxn := $subbrk/DETAIL/TRANSACTION[@Usage = 'usage'] >> >> let $DistCallTypesAll := ($detailUsageTxn/SUB_SECTION/@Type) >> let $DistCallTypes := ( for $r in $DistCallTypesAll group by $ct := $r return $ct) >> >> //Here taking nearly 3 minutes >> >> let $allmonths := distinct-values($subbrk/Month) >> let $rowcnt := count($detailUsageTxn/SUB_SECTION) >> let $months := $allmonths[position() le 10000] >> let $opttype := 'NumVal' >> let $durop := 'hh:mm:ss' return >> ( >> if($DistCallTypes!='' >> ) >> then( >> for $month in $months >> return >> <R> >> <GRPBY>{ $month }</GRPBY>{ >> if($opttype = 'NumVal') then >> for $ct in $DistCallTypes >> let $ctCnt := sum($detailUsageTxn/SUB_SECTION[data(@Type) = data($ct) and data(../../../Month) = $month]/@Calls) >> //Here taking 4 to 5 minutes >> return element >> { replace(concat('_',data($ct)),'(\.|\[|\]|\\|\||\-|\^|\$|\?|\*|\+|\{|\}|\(|\)| |')', '_') } >> {xs:decimal($ctCnt)} >> else () >> } >> >> </R>) >> else() >> ) >> >> >> >> Regards, >> Chandrasekhar >> >> >>
Written for BaseX 9.4. BaseX may have changed since: please check the current documentation.
⚡Generated with XQuery