You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@jackrabbit.apache.org by hwellmann <ha...@multi-m.de> on 2010/10/15 12:18:11 UTC
How to avoid sequential scans in queries
I'm currently trying to evaluate Jackrabbit 2.1.1 compared to more basic
solutions with JPA or Lucene.
I've started with a simple example, creating a repository with 50000 nodes
with random string properties and then querying for all nodes with a given
property value like this:
select * from 'nt:unstructured' where myProp = 'myValue'
The query returns the expected results, but it is awfully slow.
Stepping through the code, it seems to me that Jackrabbit builds a Lucene
query for items with node type 'nt:unstructured' and then iterates over the
result set with 50000 matches to filter the nodes by the property
constraint.
I would have expected the Lucene query to contain a clause for the given
property value and to return just the matching nodes, with no filtering on
the result set.
Am I missing anything? I'm just using a TransientRepository with the default
configuration.
Best regards,
Harald
--
View this message in context: http://jackrabbit.510166.n4.nabble.com/How-to-avoid-sequential-scans-in-queries-tp2996800p2996800.html
Sent from the Jackrabbit - Users mailing list archive at Nabble.com.
Re: How to avoid sequential scans in queries
Posted by sam lee <sk...@gmail.com>.
This might be interesting:
http://thread.gmane.org/gmane.comp.apache.jackrabbit.user/15557/focus=15559
I think jackrabbit will continue to support XPath and you should use XPath
or SQL, not SQL2 or JQOM at the moment.
or you fix it and submit the patch.
relevant ticket: https://issues.apache.org/jira/browse/JCR-2715
On Fri, Oct 15, 2010 at 8:46 AM, hwellmann <ha...@multi-m.de>wrote:
>
> OK. so XPath and SQL are deprecated but fast, SQL2 is current but slow (and
> has rather clumsy syntax, for my taste, compared to XPath).
>
> This raises a couple of questions:
>
> 1) On the implementation side: For a "production-ready implementation of
> JCR
> 2.0" (quoted from Jackrabbit homepage), I would expect production-ready
> performance.
>
> Meanwhile I've found similar and more detailed observations from other
> users
> (
> http://jackrabbit.510166.n4.nabble.com/Performance-of-SQL-2-versus-XPATH-td1590422.html
> ).
> I think it's ok for a reference implementation to be functionally correct
> but not optimized, but production-ready is another story...
>
> It would be fair at least to add some words of warning on the relevant wiki
> page (http://wiki.apache.org/jackrabbit/Search).
>
> 2) On the specification side: Why on earth was XPath deprecated? Even when
> the SQL2 implementation gets improved, the syntax remains cumbersome. (I
> realize this is a JCR and not a Jackrabbit question....)
>
> 3) Is it safe to implement a new application with Jackrabbit 2.x and XPath
> query syntax? Will Jackrabbit continue to support XPath in all 2.x
> releases?
>
> Best regards,
> Harald
>
>
>
> --
> View this message in context:
> http://jackrabbit.510166.n4.nabble.com/How-to-avoid-sequential-scans-in-queries-tp2996800p2996958.html
> Sent from the Jackrabbit - Users mailing list archive at Nabble.com.
>
Re: How to avoid sequential scans in queries
Posted by hwellmann <ha...@multi-m.de>.
OK. so XPath and SQL are deprecated but fast, SQL2 is current but slow (and
has rather clumsy syntax, for my taste, compared to XPath).
This raises a couple of questions:
1) On the implementation side: For a "production-ready implementation of JCR
2.0" (quoted from Jackrabbit homepage), I would expect production-ready
performance.
Meanwhile I've found similar and more detailed observations from other users
(http://jackrabbit.510166.n4.nabble.com/Performance-of-SQL-2-versus-XPATH-td1590422.html).
I think it's ok for a reference implementation to be functionally correct
but not optimized, but production-ready is another story...
It would be fair at least to add some words of warning on the relevant wiki
page (http://wiki.apache.org/jackrabbit/Search).
2) On the specification side: Why on earth was XPath deprecated? Even when
the SQL2 implementation gets improved, the syntax remains cumbersome. (I
realize this is a JCR and not a Jackrabbit question....)
3) Is it safe to implement a new application with Jackrabbit 2.x and XPath
query syntax? Will Jackrabbit continue to support XPath in all 2.x releases?
Best regards,
Harald
--
View this message in context: http://jackrabbit.510166.n4.nabble.com/How-to-avoid-sequential-scans-in-queries-tp2996800p2996958.html
Sent from the Jackrabbit - Users mailing list archive at Nabble.com.
Re: How to avoid sequential scans in queries
Posted by Alexander Klimetschek <ak...@day.com>.
On Fri, Oct 15, 2010 at 12:43, hwellmann <ha...@multi-m.de> wrote:
> Is there a problem with the SQL2 query engine? Should I use XPath instead?
It is not optimized yet (however I am quite surprised that the
property lookup is not using the index). AFAIK there was some work on
optimizing it for 2.2, but I can't give you any details.
Regards,
Alex
--
Alexander Klimetschek
alexander.klimetschek@day.com
Re: How to avoid sequential scans in queries
Posted by hwellmann <ha...@multi-m.de>.
Alexander Klimetschek wrote:
>
> On Fri, Oct 15, 2010 at 12:18, hwellmann <ha...@multi-m.de>
> wrote:
>> I've started with a simple example, creating a repository with 50000
>> nodes
>
> Are these nodes directly under one node in a flat hierarchy? Note that
> you should not have more than ~10k nodes per child node (search the
> mailing list for "flat hierarchy" for more info).
>
It's a flat hierarchy currently, but even so there should be no need to scan
all the nodes....?
>> select * from 'nt:unstructured' where myProp = 'myValue'
>
> This is JCR-SQL from the JCR 1.0 spec, right?
>
It's supposed to be Query.JCR_SQL2.
> Could you try an Xpath query and compare the execution times?
>
> //element(*, nt:unstructured)[@myProp='myValue']
>
>
I did, and this is extremely fast. I also tried a Query.SQL with slightly
modified syntax:
select * from nt:unstructured where myProp = 'myValue'
The XPath and SQL queries return almost instantly. I started with SQL2 since
the other query languages are marked as deprecated.
Is there a problem with the SQL2 query engine? Should I use XPath instead?
Best regards,
Harald
--
View this message in context: http://jackrabbit.510166.n4.nabble.com/How-to-avoid-sequential-scans-in-queries-tp2996800p2996825.html
Sent from the Jackrabbit - Users mailing list archive at Nabble.com.
Re: How to avoid sequential scans in queries
Posted by Alexander Klimetschek <ak...@day.com>.
On Fri, Oct 15, 2010 at 12:18, hwellmann <ha...@multi-m.de> wrote:
> I've started with a simple example, creating a repository with 50000 nodes
Are these nodes directly under one node in a flat hierarchy? Note that
you should not have more than ~10k nodes per child node (search the
mailing list for "flat hierarchy" for more info).
> select * from 'nt:unstructured' where myProp = 'myValue'
This is JCR-SQL from the JCR 1.0 spec, right?
> The query returns the expected results, but it is awfully slow.
>
> Stepping through the code, it seems to me that Jackrabbit builds a Lucene
> query for items with node type 'nt:unstructured' and then iterates over the
> result set with 50000 matches to filter the nodes by the property
> constraint.
This is not expected, for simple property lookups it should use the
lucene index.
Could you try an Xpath query and compare the execution times?
//element(*, nt:unstructured)[@myProp='myValue']
Regards,
Alex
--
Alexander Klimetschek
alexander.klimetschek@day.com