JCR query - slow performance when using OR?

0
votes

I have a JCR query like: 

SELECT * FROM [my:nodetype] WHERE ISDESCENDANTNODE([/somepath]) OR (otherPath LIKE '/someotherpath')

It executes in 6 seconds.

The otherPath property is a multivalue string property (only two object have it set). There are ONLY 7 total nodes of type my:nodetype in the whole database

While both separate queries:

SELECT * FROM [my:nodetype] WHERE ISDESCENDANTNODE([/somepath])

And 

SELECT * FROM [my:nodetype] WHERE otherPath LIKE '/someotherpath'

Execute in times less than 1ms. Is there any reason why the first query runs so slow? Maybe there is a possibility to see a query plan in Jackrabbit just as with SQL databases? How can i debug the problem?

I cannot imagine why searching through 7 nodes in total, no matter the search criteria would produce 7 second query run time...

1
jackrabbitjcr
I guess you are using Jackrabbit Oak, right? This is a limitation of the SQL-2 optimizer. If you use XPath, it should work fine. Ah, sorry, you can't write that query in XPath... so I'm out of options, sorry. You could use "union" (which is not part of the JCR specification, but works with Oak). – Thomas Mueller
actually i am using plain jackrabbit which is included into magnolia cms i do not think there is support for "union" there. – Pma
OK then it's probably Jackrabbit 2.x. Then I guess running two queries is the only solution you have. – Thomas Mueller
its the ISDESCENDANTNODE that slows things down. Like others have said, avoid it or workaround it by using multiple queries/xpath/checking path outside the query. – TedTrippin

1 Answers

0
votes

It's a known bug in Jackrabbit. I'm no sure but i think it's still not corrected in the latest version of Jackrabbit.

There is nothing to do about this, you have to use two queries.