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