I’m working with this documents:
2000
…
I have more than 30000 “Segment” documents in my collection. All of them have follow attributes values:
SessionId = 100
Ref = 2
I execute queries like:
let $c := (for $b in input()/Segment
where ($b/@SessionId = 100) and ($b/@Ref = 2) return $b)[position()=25743] return $c
It runs quikly (less than 1 second), but I need sort my documents by “InitTime” and I use this one:
let $c := (for $b in input()/Segment
where ($b/@SessionId = 100) and ($b/@Ref = 2) return $b sort by($b/InitTime))[position()=25743] return $c
but his response time is very big (about 15 seconds).
Attributes “SessionId” and “Ref” and “InitTime” element are indexed by standart mode.
How can I improve the query performance ?
thanks
Hi,
your first query returns the 25743rd Segment element that has the required property.
Your next query returns the 25743rd Segment element that has the required property
after all such elements have been sorted.
No wonder the second query takes longer. In addition to the first it has to read all Segments and sort them all. Never the less 15 seconds is too much.
A possible explanation is that the sort is not done by index but by scanning the
documents for their InitTime values. You can find this out by preceding the query
with {?explain?}. In the output you’ll find an XqcUnnestStdIdxScan element that
contains those query steps that will be dealt with by using an index when the query
gets executed. For your second query these should be the filter condition (an XqcAlgAndFunction having two XqcAlgStdIdxPredicate children) and the expression used
for sorting (XqcAlgSortSpec).
If this is not the case, the index on InitTime is not used. A possible reason for
that is that the index is not properly defined. Another reason might be a small error
in your query (The sort spec should be (InitTime) instead of ($b/InitTime) since $b
is not defined in parts following the respective return clause), but as far as I know
all Tamino versions that still accept this treat it just like the proper sort spec. A third reason is, of course, a bug in our XQuery implementation. To judge this, I need
info on precisely which version you are running and your schema.
Regards,
Juliane.