Letterpart
asked on
XQuery performance - is unordered the answer
I have an XQuery performance question I hope someone can assist with.
The code below is working fine but I would like to improve performance if possible.
What it is doing is ...
- getting all the distinct values of the prodname attribute found in the hits
then
- works out how many times each distinct value occurs in the hits
- returns those distinct values in order along with the total for each
I sometimes have up to 12000 items in $hits so the whole process can take a while (longer than I would like it too anyway).
I have read that using unordered expressions/functions can have a significant improvement on performance.
So, my question is, is there a way of improving the performance of the above - using unordered or any other way - and what coding changes would need to be made?
I would still need to keep the "order by $d" line 5 in order to keep the distinct values in alpha order for the return
The code below is working fine but I would like to improve performance if possible.
What it is doing is ...
- getting all the distinct values of the prodname attribute found in the hits
then
- works out how many times each distinct value occurs in the hits
- returns those distinct values in order along with the total for each
I sometimes have up to 12000 items in $hits so the whole process can take a while (longer than I would like it too anyway).
I have read that using unordered expressions/functions can have a significant improvement on performance.
So, my question is, is there a way of improving the performance of the above - using unordered or any other way - and what coding changes would need to be made?
I would still need to keep the "order by $d" line 5 in order to keep the distinct values in alpha order for the return
let $tempResult :=
for $d in distinct-values($hits/ancestor-or-self::DOCUMENT/@prodname)
let $q := $hits/ancestor-or-self::DOCUMENT[@prodname = $d] (: all the hits where prodname attribute has value of $d :)
order by $d
return <item zprodname="{$d}" zprodnamenum="{count($q)}"/>
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
welcome Paul
ASKER
Further testing showed me that the bottleneck was the queries of $hits which occurred distinct-values of @prodname times - getting the distinct-values was OK.
I was able to create an index on the @prodname attribute and that dramatically improved performance.
cheers,
Paul