SQL2 search query taking too long to execute.

Take a look at this thread on the Apache site - it explain an issue with your query - mainly use of ISDESCENDANTNODE:

https://issues.apache.org/jira/browse/JCR-2835

i went through the jira page, but wasn't this fixed in the 2.3.0 version of apache jackrabbit? Looking at my system/console/bundles page, CQ 5.6.1 uses 2.6.2 version of apache jakrabbit api.

Also i tried the equivalent query with the SQL version (using jcr:path LIKE constraint) and that too took about the same time.

Hi Jörg,

We face the same performance issue using SQL and SQL2 query.

Could you please elaborate more on what are the other criteria's for improving the performance?

Hi,

In [1] I tried to explain some aspects, but I probably have to cover them more deeply. Can you share your slow query as well? Or is it the same as in the original posting?

Regards,
Jörg

[1] http://cqdump.wordpress.com/2012/11/28/ways-to-access-your-content-with-jcr-part-2-performance-aspects/
 

Hi Jörg,

The below are the sample queries that we make use of:

SQL - SELECT * FROM CUSTOMNODETYPE WHERE jcr:path like '/content/foo/bar/%/level1/level2/%' and cq:propName='val'

SQL2 - SELECT * FROM [CUSTOMNODETYPE] as t where ISDESCENDANTNODE('path_under_required_folders') and t.isModified=CAST('true' AS BOOLEAN)

I went through your post and for our large repository and for the use cases where the data needs to be obtained from a considerable large subtree, i believe we should be using jcr search using queries. 

just a FYI the combined size of our index is around 18 GB (crx-quickstart/repository/repository/index && crx-quickstart/repository/workspaces/crx.default/index), which lead me to the documentation @ http://helpx.adobe.com/experience-manager/kb/SearchIndexingConfig.html which described the need to index only certain properties of node types. Would that help?

Hi,

If you need to search a large subtree, the use of a query can be suitable. In your case, you probably have a large number of results when you look for your custom nodetype; each of these nodes needs to get checked against the TarIndex to look up the path. I don't know the query optimizer of JCR that well, but you can try a simple reordering of the conditions first (the check of a property against a specific value is quite fast). Depending on the size of the tar index the TarPM option "indexInMemory" could also speedup this search.

If that doesn't help and you need to speedup the query, you should consider to denormalize your data, so you create more specific queries which can be handled solely by Lucene. But that requires often changes on a number of places in your application code.

Regarding the optimization of the index.You can do this, but it needs good testing. When you have created own nodetypes, you can be more agressive; I would recommend to avoid to "tune" the index of the ootb nodetypes.

Jörg