6 Replies Latest reply on Apr 21, 2013 9:47 PM by Brian Wallis

    slow SQL2 query

    Brian Wallis Master

      {code}select e.* from [inf:episode] as e inner join [inf:patient] as p on isdescendantnode(e,p) where p.[inf:masterId] = '00010014'{code}

       

      this takes about 8 seconds to execute. The schema is simple

       

      {code}[inf:patientId]

                - inf:ns (STRING) mandatory COPY

                - inf:id (STRING) mandatory COPY

       

      [inf:patient] > mix:versionable, nt:hierarchyNode

                + * (inf:patientId) COPY

                + * (inf:section) SNS COPY

                - inf:masterId (STRING) mandatory COPY

                - inf:masterNs (STRING) mandatory COPY

       

       

      [inf:section] > nt:hierarchyNode orderable

                - inf:name (STRING) mandatory COPY

                + * (nt:hierarchyNode) SNS VERSION

       

      [inf:episode] > nt:hierarchyNode

                + * (inf:document) SNS VERSION

                - inf:ns (STRING) mandatory COPY

                - inf:id (STRING) mandatory COPY

      {code}

      but I have a large number of nodes

       

      • 750  [inf:patient]
      • 6000 [inf:section]
      • 10453 [inf:episode]
      • 36675 [inf:document]

       

      I seem to recall that some one mentined that there is a way to get a query plan from modeshape but cannot find out how now. I'm running my query via the rest API (using curl) and I do get the expected result (15 episode nodes).

       

      So, how do you do an "explain" and any suggestions as to how to make this query perform reasonably? All the non-join queries are taking a few milliseconds to execute.

       

      Running a recent 3.2-SNAPSHOT build on JDK 1.7.0_17. Indexes are in the filesystem, nodes in PostgreSQL, modeshape is running in JBoss EAP 6.1

        • 1. Re: slow SQL2 query
          Brian Wallis Master

          I think this problem has been covered in this post, https://community.jboss.org/thread/222854?tstart=0

           

          Join performance is just not particularly good for the moment.

          • 2. Re: slow SQL2 query
            Randall Hauch Master

            I seem to recall that some one mentined that there is a way to get a query plan from modeshape but cannot find out how now. I'm running my query via the rest API (using curl) and I do get the expected result (15 episode nodes).

            I don't think the REST API has a way to get the query plan. Feel free to log an enhancement request.

            • 3. Re: slow SQL2 query
              Horia Chiorean Master

              Atm, one can retrive the query plan by using the ResultSet and ModeShape's API, like so: (org.modeshape.jcr.api.query.QueryResult)result).getPlan()

              • 4. Re: slow SQL2 query
                Brian Wallis Master

                I've tried to come up with some alternative queries that don't involve joins

                 

                {code}

                select e.[jcr:path],e.[inf:ns],e.[inf:id] from [inf:episode] as e where isdescendantnode(e,(select p.[jcr:path] from [inf:patient] as p where p.[inf:masterId] = '00010014'))

                {code}

                 

                This doesn't work, it gets the error:

                 

                is not well-formed: Expecting ')' but found 'select' at line 1, column 93: isdescendantnode(e,( ===>> select p.[jcr:path]

                 

                The documentation sort of suggests this should work, for subqueries it says

                 

                Additionally, because subqueries appear on the right-hand side of an operator, all subqueries must return a single column, and each row's single value will be treated as a literal value.

                 

                and isdescendantnode() says

                 

                The descendant-node constraint stipulates that the node appearing in the selector with the given name is a descendant of a node with a path that matches the literal path provided.

                 

                but I suppose a literal value is not quite a literal path.

                 

                Next I tried using jcr:path and like, the following query runs in less than 50ms and returns the results I expect (the statement "select p.[jcr:path] from [inf:patient] as p where p.[inf:masterId] = '00010014'" returns the path /inf:patient[265])

                 

                {code}

                select e.[jcr:path],e.[inf:ns],e.[inf:id] from [inf:episode] as e where e.[jcr:path] LIKE '/inf:patient[265]%'

                {code}

                 

                Which got me wondering if I could use a subquery to build up the value for the RHS of the LIKE constraint (since it needs the trailing '%'). I tried the following

                 

                {code}

                select e.[jcr:path],e.[inf:ns],e.[inf:id] from [inf:episode] as e where e.[jcr:path] LIKE (select concat(p.[jcr:path],'%') from [inf:patient] as p where p.[inf:masterId] = '00010014')

                 

                or

                 

                select e.[jcr:path],e.[inf:ns],e.[inf:id] from [inf:episode] as e where e.[jcr:path] LIKE (select p.[jcr:path]||'%' from [inf:patient] as p where p.[inf:masterId] = '00010014')

                {code}

                 

                but they return errors since the concat or its alias '||' are not supported.

                 

                is not well-formed: Expecting \"FROM\" but found \"(\" at line 1, column 105:  LIKE (select concat ===>> (p.[jcr:path],'%') f

                 

                Does anyone have any other possibilities that I haven't thought of?

                 

                thanks.

                • 5. Re: slow SQL2 query
                  Brian Wallis Master

                  Just got the dreaded "moderator" message posting a reply and this time the reply has not turned up, wonder if this one will get through...

                   

                  [edit: Yep, just my first one that failed, oh well, just have to wait I suppose]

                  • 6. Re: slow SQL2 query
                    Brian Wallis Master

                    Added three enhancement requests to help with understanding and creating better queries

                    MODE-1901, MODE-1904 and MODE-1905.