When I call VersionManager checkin method, I do see too many SQL SELECT queries

[sivaa]

When I call Version Manage checkin method I do see too many SELECT queries like like "SELECT CONTENT FROM MY_REPOSITORY WHERE Id = ?" being fired. Though we have enough Workspace cache size, we still see this query being fired. I observed that the SQL queries are being made in  org.modeshape.persistence.relational.DefaultStatements.getById() method. Any quick help is really appreciated.

[wesssel]

In order to diagnose any problems you should definitely provide some more information about the use case. Think of: number of child nodes, node structure, etc.

[sivaa]

Hi Wessel, we have deep nested structure which looks like below

root - contains multiple store nodes

*store (nt:folder) - contains one ‘Props‘& multiple ‘res’

    - Props (nt: folder) - contains list of propNodes

        - propNode (n:folder) - It contains JCR properties

    - *res (nt:folder) -contains nested folders,multiplefiles

        - *file (n:file) - contains jcr:data

Every node above is versionable(mix:versionable).

I am trying to import a store node which contains 380 child nodes in total and also create a version for each of these nodes using VersionManager.checkin. What I observe is closely 12000 select queries are being fired which is a kind of DB explosion. We defined 30000 workspace cache nodes and have indexes defined on ‘propNode’ and ‘res‘ by its JCR:PATH in repository config json. Any solution to cut down the SELECT SQL queries?

[wesssel]

I think the problem is that every node is versionable:

https://modeshape.wordpress.com/category/performance/

Large parent should not be versionable. When a parent contains lots of children, make sure that the parent’s node types and mixins are not mix:versionable, and that all child node definitions have an on parent versioning of ignore. This allows ModeShape to speed up quite a few potentially-expensive operations, including addNode(…).

[sivaa]

It looks like every parent adds 'x' number of SELECT queries for each of its child,  in my case x is 8, which is worry some. Not only that there are few more observations which are related to slowness, like Node.getIdentifier method which is returning an opaque identifier and when I query a node with this opaque identifier using JcrSession.getNodeByIdentifier, it makes unnecessary call to DB first searching for it and then recomputes the actual nodekey to fetch the node. Because of the fact that opaque identifier do not exist in DB, it is scanning the entire DB, which eventually takes more time as the number of nodes increase. Any solution to escape with this check and hit it right the first time?