JCR SQL2 query generating hundreds of SQL statements for a simple query
We have a Modeshape application that is backed in MySQL in a MODESHAPE_REPOSITORY table.
One of my nodes is of type "tba:feedTemplate"
When i do a select to find all the nodes of this type : "SELECT e.* from [tba:feedTemplate] AS e" i see in mySQL it is producing 500+ SQL statements querying all sorts of items, especially when its executing the NodeIterator.hasNext() call.
Is there anyway to tune Modeshape so its not generating so many individual select statements in MySQL?
I am curious as to why its doing so many selects when attempting to get a few nodes in the system ?
I am using Modeshape 5.4.1.Final.
Any help is appreciated. Below are some details:
My Java JCR Query Code Snippet
String queryString = "SELECT e.* from [tba:feedTemplate] AS e ";
Query query = session.getWorkspace().getQueryManager().createQuery(queryString, "JCR-SQL2");
QueryResult result = query.execute();
if (result != null) {
NodeIterator nodeIterator = result.getNodes();
while (nodeIterator.hasNext()) { //This call kicks off hundreds of SQL statements
Node node = nodeIterator.nextNode();
...
}
}
MySQL Queries generated, especially after the NodeIterator.hasNext() call
Even though I only have 1 tba:feedTemplate in my system i find it will generate hundreds of queries like the following replacing the <id> with different ids:
SELECT CONTENT FROM MODESHAPE_REPOSITORY WHERE ID = <id>
Modeshape cnd files that might help.
Below are the cnd definition for tba:feedTemplate
[tba:feedTemplate] > tba:entity, mix:title, tba:propertied, tba:accessControlled
- tba:defineTable (BOOLEAN) = 'false'
- tba:dataTransformation (BOOLEAN) = 'false'
- tba:allowPreconditions (BOOLEAN) = 'false'
- tba:icon (STRING)
- tba:iconColor (STRING)
- tba:nifiTemplateId (STRING)
- tba:state (STRING) = 'ENABLED'
- tba:json (STRING)
- tba:feeds (REFERENCE) multiple
- tba:order (LONG)
- tba:isStream (BOOLEAN) = 'false'
- tba:templateTableOption (STRING)
[tba:propertied] mixin
+ tba:properties (tba:properties) COPY
[tba:feed] > tba:extensibleEntity, mix:referenceable, tba:accessControlled
+ tba:summary (tba:feedSummary) = tba:feedSummary autocreated mandatory
+ tba:data (tba:feedData) = tba:feedData autocreated mandatory
