Execution time Xquery

, ,
1

Hello,

I do an Xquery like this:

for $i in input()/Locali[id>=0 and id<=3] return $i

The field Locali/id is an index.

The query take a lot of time.

Can you tell me why and if I have to do something especial?

2

Unfortunately it is not possible to provide a definitive answer based on the information that you have supplied.

It would be useful to know the complexity of the schema and the number of documents in the database. Hopefully you are also using Tamino 4.4 as 4.2 is quite old.

However, the query may execute faster if you use the position() function. An example:


(for $i in input()/Locali[id>=0 and id<=3] return $i)[position() <= 20]
3

The problem is that if I execute

for $i in input()/Locali[id=0 or id=2 or id=3] return $i)

the answer is fast

but if I execute

for $i in input()/Locali[id>=0 and id<=3] return $i

the answer is very slow.

It must seem that the query doesn’t use the index Locali/id

the number of documents in the database is 53000000.

4

just curious…how many documents are there for a given Locali/id value - is “id” a unique value per document or are there many documents with the same id value? Your question on id=0 or id=2 or id=3 omitted id=1 - was that just a typo or do you actually not want id=1 in the results, as it would be included in id >=0 and id <=3?

5

I think the problem relates to whether the XQuery is optimised or not.
There is a section in the documentation called “Performance Guide”, “Efficient Queries: XQuery”, “Value Range Predicates”. This tells you that for optimization to be effective “Node expression must be a variable (and) variables must be equal for both comparisons.”
In your case, the node expression is “id>=0 and id<=3” so “id” is not a variable. If you look at the analysis of your original XQuery in the Interactive Interface, it probably looks like two index lookups inside a logical “and” function. This will be slow if the index lookups return many results to be ANDed together.
Now try to change your XQuery to use variables so that optimization happens correctly:

for $i in input()/Locali
let $x := $i/id
where $x >=0 and $x <=3 
return $i

If you analyse the new XQuery you should see a single Index Scan containing a ValueRangeIndexPredicate. This should be faster.
HTH

6

Hello,

thank you very much.

I try it and it is very fast.

Best regards,

Mark Foster.