XQuery with >, <, gt, lt too slow

, ,
1

Hi,

We have a doctype with an index defined over three fields (obra, par and posPar). The following XQuery
for $a in input()/A
where $a/obra=398 and $a/par=557 and $a/posPar>=2
return $a

is very slow, while this one:
for $a in input()/A
where $a/obra=398 and $a/par=557 and $a/posPar = (2, 3, 4, 5, 6, 7, 8, 9)
return $a

is executed inmediately.

The problem is the “>” operand (or “>=” or “ge”) What’s the correct way to write the first XQuery?

Thanks in advance?

2

Hi Pau,
A few extra info’s would be interesting:

  1. what datatype and indextype has posPar ?
  • is there an index ?
  1. how many instances are there with values beyond 9 ?
  • is the bulk of of hits > 9 then the explanation would be obvious :wink:

regards
Finn

3

Hi Finn,

  1. what datatype and indextype has posPar ?
    The datatype is xs:integer and the indextype is standard
  • is there an index ? Yes.
  1. how many instances are there with values beyond 9? There are 8 (2, 3, 4, 5, 6, 7, 8 and 9)

The result set should be the same. I don’t understand this extremely slow behavior for “the same” XQuery…

regards,

4

Hi Pau,
So you mean there are no posPar values higher than 9 ?
What does the “explain” says about indexUsage ?

Finn

5

Hi Finn,

Yes, there are no posPar values higher than 9.

The “explain” says the same about indexUsage for both XQueries. I paste here the response:

<?xml version="1.0" encoding="windows-1252" ?>

{?explain ?}
for $a in input()/A
where $a/obra=398 and $a/par=557 and $a/posPar lt 9
return $a

]]>
</xq:query>

Thanks in advance,

6

Hi Pau,
May I recommend that you use the XQUERY tool which does a translation of the explain output into a more readable format :wink:
Finn

7

Hi Finn,

The “Explain Query” of the XQuery tool answers this:

"
This query compiles successfully.

This query will perform an index scan (details unknown).

The analyzed query:
{?explain?}
for $a in input()/Locali
where $a/obra=398 and $a/par=557 and $a/posPar = 2
return $a
"

And the “Response” tab shows the same message than the Interactive Interface.

:frowning:

regards,

8

Hi Pau,

  • then I’m beginning to run out of suggestions ! :frowning:

How many hits do you get/want ?

  • Have you tried specifying a position filter to see if this show the same difference in execution time ?

Finn

(for $a in input()/A
where $a/obra=398 and $a/par=557 and $a/posPar>=2
return $a ) [position() < 10 ]

9

Hi Finn,

I found another XQuery which executes efficiently. I just separated the criteria into two pieces. In one hand, the “equals” comparison, and in the other hand the “less than or equals” comparison. Just like this:

for $a in input()/A [posPar <= 9]
where $a/obra=398 and $a/par=557
return $a

I don’t know why Tamino likes this one but not the other one. But it works.

Thanks for your attention,

Best regards.