Poorly Performing XQuery

, ,
1

Hi I have been working to increase performance of an XQuery, which brings back about 300 records, which isn’t alot

The query goes through each facility (of which there are around 40) checking them against the PEA (of which there are around 900). I have removed parts of the query to find the bottleneck and it appears to be the looping of the PEA, but I have no other way to do this. The query occasionally times out and is impossible to use in my web application as it crashes :cry:. My only other alternative is to pull out the Facility list and PEA list and use XSLT to search, but this isn’t really using Tamino! Any ideas :?:


declare namespace xs = "http://www.w3.org/2001/XMLSchema"

let $startDate := xs:dateTime('2000-01-30T00:00:00-05:00'), 
    $endDate := xs:dateTime('2015-06-03T00:00:00-05:00'),
    $facilityID := 527, {-- 50 513 --}
    $subordinateFacilities := input()/financialPoint[@id = $facilityID]/child
    $allPeas := input()/pea[statusHistory/statusEvent [dateTime gt $startDate][dateTime lt $endDate]]
        [not(currentStatus/status = 'inPreparation')]
return
    for $facility in $subordinateFacilities
    let $peas := $allPeas[profitCenter/facilityID = $facility]
where count($peas) gt 0 return
    let $gms := for $p in $peas return $p/authorizationRoles/authorizationRole[roleId = 'general-manager']/incumbentLDAPUid
    let $lcs := for $p in $peas return $p/authorizationRoles/authorizationRole[roleId = 'local-controller']/incumbentLDAPUid
return 
    <facility-details>	
        <id>{ $facility/text() }</id>
            <general-manager>{ for $gm in distinct-values($gms) return 
                <incumbent>
                <ldapUid>{$gm}</ldapUid>
                    <selectedByList>{let $gmPeas := $gms[. = $gm]/../../..
                        for $initiatedBy in distinct-values ($gmPeas/initiatedByLDAPUid) return
                        <selectedBy>
                        <ldapUID>{$initiatedBy}</ldapUID>
                        <detailsList>{ for $thisGmPea in $gmPeas[initiatedByLDAPUid = $initiatedBy] return
                           <detailsEntry>{
                              $thisGmPea/id,
                              $thisGmPea/statusHistory/statusEvent[status =  'create']/dateTime
                           }</detailsEntry>
                        }</detailsList>
                     </selectedBy>
                  }</selectedByList>
               </incumbent>
            }</general-manager>
            <local-controller>{ for $lc in distinct-values($lcs) return 
               <incumbent>
               <ldapUid>{$lc}</ldapUid>
               <selectedByList>{let $lcPeas := $lcs[. = $lc]/../../..
               for $initiatedBy in distinct-values ($lcPeas/initiatedByLDAPUid) return
                  <selectedBy>
                  <ldapUID>{$initiatedBy}</ldapUID>
                     <detailsList>{ for $thisLcPea in $lcPeas[initiatedByLDAPUid = $initiatedBy]	return
                     <detailsEntry>{
                        $thisLcPea/id,
                        $thisLcPea/statusHistory/statusEvent[status = 'create']/dateTime
                     }</detailsEntry>
                  }</detailsList>
               </selectedBy>
            }</selectedByList>
         </incumbent>
      }</local-controller>
   </facility-details>
2

Hi,

This is a pretty complex query. Without any information regarding the available indexes it is hard to say what exactly causes your performance problems. To get assistance to make the query running faster you should really contact your Tamino support. Please provide them the Tamino version, the query and the schema.

Best Regards,

Thorsten Fiebig