1 Reply Latest reply on Sep 24, 2017 4:10 PM by adam.mccormick

    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:




      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

        • 1. Re: JCR SQL2 query generating hundreds of SQL statements for a simple query

          I'm pretty sure the result of the query is going to be a list of references to the found nodes. Modeshape will still have to load each node from the database if it isn't already in the workspace cache. If your query found 500 nodes Modeshape will have to load 500 nodes from the database hence the 500 "select by id" calls. What happens if you run this query a second time? Do you still see 500 database queries? I would expect a lot more workspace cache hits on the second run.