I have 11,000 nodes with a mixin type of app:asset that sit under a parent node /assets, and approximately 330,000 nodes total in the repository. The asset nodes are descendent nodes, not direct children as we used the UUID approach described in this article Improving performance with large numbers of child nodes | ModeShape to distribute the nodes (over time we expect the number of nodes to be in the millions).
I have a need to be able to return a paginated result set of asset nodes ordered by created date descending. I currently use the following query to return the first 10 assets:
select node.* from [app:asset] AS node WHERE ISDESCENDANTNODE("/assets") ORDER BY node.[jcr:created] LIMIT 10 OFFSET 0;
In order to optimize the query I have created an index (using Lucene)
<index name="asset_types" provider-name="lucene" synchronous="true" node-type="app:asset" columns="jcr:mixinTypes(NAME)"/>
I have a cache configured that can hold the entire repository in-memory so no access the the caches persistent store should be required (using JDBC).
The query on average takes about 4 seconds to return which is considerably longer than I would like to bring back a result set of 10 nodes. The query is using the asset_types index (I have logged the query plan to confirm this). Does anyone have any suggestions on how I could improve the performance of this query or where and why it is taking 4+ seconds to execute?
Under /assets/ab/cd/asset1 I have a 2 child nodes with a mixin type app:variant out of a total of ~30 nodes. If I run the following query:
select node.* from [app:varitan] AS node WHERE ISDESCENDANTNODE("/assets/ab/cd/asset1") ORDER BY node.[jcr:created] LIMIT 10 OFFSET 0;
It takes 5+ seconds to return the 2 nodes of app:variant. In this scenario I have not indexed the node type 'app:variant' as doing so results in all variant nodes across all assets being returned and filtered based on the ISDESCENDANTNODE constraint which normally timeout. Again does anyone have any suggestions on how I could improve the performance of this query or where and why it is taking 5+ seconds to execute?
In both cases I used the following configuration when specifying my index provider
<index-providers> <index-provider name="lucene" classname="lucene" path="modeshape/indexes/" relative-to="jboss.server.data.dir" module="org.modeshape.index-provider.lucene"/> </index-providers>