[OAK-9286] Running query to fetch a node repository takes ~60 secs

• Type: Bug
• Status: Open
• Priority: Critical
• Resolution: Unresolved
• Component/s: indexing, lucene, property-index, query, search
• Assignee: Unassigned
• Reporter: Vishnu Aggarwal
• Created: 26/Nov/20 10:55
• Updated: 26/Nov/20 16:18

Description

We have an oak repository set up as the following structure under the root node with 4 different node types namely. One of the nodeTypes (cm:media) has ~10K nodes already created for it.

We run the following query to get the desired result:

SELECT * FROM [cm:mediaFile] as node WHERE ISDESCENDANTNODE(node, "/mediafiles") AND LENGTH(node.[cm:oldId]) > 0 AND node.[cm:oldId] = "c6a958ef-2060-4599-ac72-d0a96d062180"

While running the application on local machine, the query returns the result in ~5 seconds while running the same query on the server, it takes around ~60 seconds.

 

The stack being used is:

Grails 3.3.5

Java 8

Mysql 5.7.31 (on local) / aurora-mysql-5-7 - 5.7.12 (on server)

OAK 1.9.8

 

The repository has been constructed in the following manner:

DocumentNodeStoreBuilder documentNodeStoreBuilder = RDBDocumentNodeStoreBuilder.newRDBDocumentNodeStoreBuilder().setRDBConnection(oakDataSource)
documentNodeStoreBuilder.createMissingLastRevSeeker()
documentNodeStoreBuilder.createVersionGCSupport()
DocumentNodeStore documentNodeStore = documentNodeStoreBuilder.build()

repository = new Jcr(new Oak(documentNodeStore)).createRepository()

and, fetching the results through

session.getWorkspace().getQueryManager().createQuery(criteria, queryLanguage).execute().getNodes()

Any help or pointers on how it can be fixed?