Hi!
we have two types of documents: A and B, each B document has an identifier that attach it to one A document. Each A document can have zero, one or more B documents attached.
We want to know how many A documents have one or more B documents attached, for it we use the following easy query:

count( for $a in input()/A
where (
for $b in input()/B
where ($b/@RefId=$a/@Id)
return $b
)
return $a
)

The atributtes “RefId” and “Id” are longs and have standard indexs.
The response time of this query is more than 30 seconds in a database with 20000 of A documents and 1000000 of B documents.

Otherwise the following similar query:

let $s := (for $a in input()/A
where (
for $b in input()/B
where ($b/@RefId=$a/@Id)
return $b
)
return $a
) return $s[position()<10]

is faster.

How can we improve the first query response time ?

Thanks in advanced

Hi,

The reason why the count query is slower than the position range query is due to the fact that for the count query the complete join result has to be determined. Whereas for the position range query the first 10 results are sufficient. For joining big document sets Tamino offers the “index-onl” join method. Unfortunately this method doesn’t work for your queries, which are semi-join queries. But if your queries consider a 1 to 1 relationship you can rewrite them. In order to activate the “index-only” join method you have to restate your count query in the following way:

{?optimization join=“index-only”?}
count(
for $a in input()/A
for $b in input()/B
where ($b/@RefId=$a/@Id)
return $a
)

The rewritten query does a “natural” join, but for a 1 to 1 relationship it should give the same result. The first line is a Tamino XQuery processing instruction that gives the query processor the hint to apply the “index-only” join method. The Tamino XQuery processing instruction and rewriting can also be applied to the position range query:

{?optimization join=“index-only”?}
(
for $a in input()/A
for $b in input()/B
where ($b/@RefId=$a/@Id)
return $a
)
[position() < 10]

If your queries don’t consider a 1 to 1 relationship you should raise a support issue.

Best Regards,

Thorsten Fiebig