we are facing on reports generation using xQuery/xPath. in this case we are getting data from 54000 xml files. it is taking more than 23 hours for generating report.

My hardware :

Linex and ram 25 Gb

And Please check given below my query:

Multilevel activity Summary report (service number  level drill down report):1- day

declare function local:getLabelName($serNo as xs:string) {   let $doc := collection('LabelCollection_1573625140685') for $label in $doc//row[@ser_no = $serNo] order by $label/@upDate descending return $label   };declare function local:getHHMMSSFromNumber($num as xs:integer) {  let $ss := xs:integer($num mod 60)  let $mm1 := xs:integer($num div 60)  let $mm := xs:integer($mm1 mod 60)  let $hh := xs:integer($mm1 div 60)  let $hhFinal := if(string-length(xs:string($hh)) >= 2) then $hh else (concat('0', $hh))  let $mmFinal := if(string-length(xs:string($mm)) >= 2) then $mm else (concat('0', $mm))  let $ssFinal := if(string-length(xs:string($ss)) >= 2) then $ss else (concat('0', $ss))  return concat($hhFinal,':',$mmFinal,':',$ssFinal) }; declare function local:getMinutesFromDutation($duration as xs:string) {   let $duration := tokenize($duration, ":")   let $hh := $duration[1]   let $mm := $duration[2]   let $ss := $duration[3]   return number(data($hh)) * 60 + number(data($mm)) + floor(number(data($ss)) div 60) }; declare function local:getViewPontChargesData() {           let $Rows := (collection("867509_Voice_OCTOBER-19_Unbilled_1")/SUBCUSTBRK[AccNo[@NO = (6945045)]][Site[@NAME='']][DOB >='2019-10-01' and DOB <='2019-10-31'])        let $serviceNumbers := string-join(distinct-values(data($Rows/CONN/@NO)),',')          let $eachServiceNumber := tokenize($serviceNumbers, ",")         for $service at $index in $eachServiceNumber          let $serviceNumber := $service      let $serviceData := $Rows[CONN[@NO = $serviceNumber]]/DETAIL/TRANSACTION/SUB_SECTION          let $nCalls := xs:decimal(sum(data($serviceData/@Calls)))          let $totalAmt := round-half-to-even(sum(data($serviceData/@Amt)), 3)          let $BNCount := count(data(distinct-values($serviceData/../../../CONN/@NO)))    let $durSum := sum(data($serviceData/@Duration))      let $totDur := local:getHHMMSSFromNumber(xs:integer($durSum))          let $tMs := local:getMinutesFromDutation($totDur)          let $totalDurSec := $durSum         return  <ROW><SNB>{ $serviceNumber }</SNB><SNL>{ distinct-values(local:getLabelName($serviceNumber)[position()=1]) }</SNL><CLS>{ $nCalls }</CLS><DUR>{ $totDur }</DUR><DUS>{$totalDurSec}</DUS><MIN>{ $tMs }</MIN><AM>{                              if($tMs > 0 and $nCalls > 0) then                      (let $avgMin := round-half-to-even($tMs div $nCalls, 2)                       return $avgMin)                              else (0.00)                            }</AM><AMT>{ $totalAmt }</AMT><PC>{                               if($totalAmt > 0 and $nCalls > 0) then                                 (let $avgPeRCall := round-half-to-even($totalAmt div $nCalls, 2)                                  return $avgPeRCall)                               else (0.00)                             }</PC><PM>{                                if($totalAmt > 0 and $tMs > 0) then                                  (let $avgPerMin := round-half-to-even($totalAmt div $tMs, 3)                                   return $avgPerMin)                                else (0.00)                              }</PM><PN>{                                if($totalAmt > 0 and $BNCount > 0) then                                  (let $avgPerNum := round-half-to-even($totalAmt div $BNCount, 3)                                   return $avgPerNum)                                else (0.000)                              }</PN></ROW>       }; let $viewPointCData := local:getViewPontChargesData() let $TotCalls := xs:decimal(sum(data($viewPointCData/CLS))) let $TotAmt := sum(data($viewPointCData/AMT)) let $totalDur := local:getHHMMSSFromNumber(xs:integer(sum($viewPointCData/DUS))) let $totalMins := local:getMinutesFromDutation($totalDur) let $TAPAmt := sum(data($viewPointCData/PN)) let $TotNoofAccs := count(data($viewPointCData/SNB)) return   (     if(count($viewPointCData) > 0) then       (for $eachRow in $viewPointCData        let $eachAmount := $eachRow/AMT        return          <R>{     $eachRow/node(),     <PER>{      if($eachAmount > 0 and $TotAmt > 0) then        (let $perAll := round-half-to-even(($eachAmount div $TotAmt) * 100, 2)         return $perAll)      else (0.00)    }</PER>   }</R>,        <R><SNB>Total/Average</SNB><CLS>{ $TotCalls }</CLS><DUR>{ $totalDur }</DUR><MIN>{  format-number($totalMins,'0.000') }</MIN><AM>{                   if($totalMins > 0 and $TotCalls > 0) then                     (let $avgMinutes := round-half-to-even($totalMins div $TotCalls, 2)                      return  format-number($avgMinutes,'0.000'))                   else (0.000)                 }</AM><AMT>{  format-number($TotAmt,'0.000') }</AMT><PC>{                  if($TotAmt > 0 and $TotCalls > 0) then                    (let $avgCallAmt := round-half-to-even($TotAmt div $TotCalls, 2)                     return  format-number($avgCallAmt,'0.000'))                  else (0.000)                          }</PC><PM>{                             if($TotAmt > 0 and $totalMins > 0) then                     (let $avgPerMin := round-half-to-even($TotAmt div $totalMins, 3)                      return  format-number($avgPerMin,'0.000'))                             else (0.000)                           }</PM><PN>{                             if($TAPAmt > 0 and $TotNoofAccs > 0) then                     (let $avgNumAmt := round-half-to-even($TAPAmt div $TotNoofAccs, 3)                      return  format-number($avgNumAmt,'0.000'))                             else (0.000)                           }</PN><PER>100</PER></R>)     else ()   )

Please suggest way forward.

Regards,

YSL