11 Replies Latest reply on Jan 7, 2013 1:02 PM by Adnan_N

    Query Performance Optimization

    Adnan_N Level 1

      I am running following query on my CQ5 environment and it taking about 15 secs to execute

       

      type=dam:Asset

      orderby=@jcr:content/jcr:lastModified

      orderby.sort=desc

      p.offset=0

      p.limit=10

      group.p.or=true

      group.0_path=/content/dam/node1

      group.1_path=/content/dam/node2

       

      As soon as I remove orderby it come down to 0.1 sec

       

      type=dam:Asset

      p.offset=0

      p.limit=10

      group.p.or=true

      group.0_path=/content/dam/node1

      group.1_path=/content/dam/node2

       

      Any thing I am missing? or should change?

       

      Thanks,

        • 1. Re: Query Performance Optimization
          Jörg Hoh Adobe Employee

          In Jackrabbit the total time for a response consists of 2 parts:

          • The time for doing the search
          • The time for retrieving the result set

           

          If you have the "orderby" statement in your query, the engine retrieves all results from the repository to perform the ordering of the result set. In your second query the query returns as soon as it has retrieved ${resultFetchSize} (default: 50) results from the repository.

          So in case you use the "orderby" statement your query might result in 50k nodes, all of these nodes are retrieved and sorted to actually deliver the top 10 nodes. If you don't use "orderby", only the first 50 nodes are loaded and then 10 are returned.

           

          And in your case also caching might effect the performance.

           

          cheers,

          Jörg

          • 2. Re: Query Performance Optimization
            reusr1 Level 2

            have you considered increasing the bundle cache size? That may help with

            the query performance. Another question is if the query needs to be done

            often or if there is some way to cache the result for some time (at

            least until there is a modification in the content tree).

             

            Ruben

            • 3. Re: Query Performance Optimization
              Adnan_N Level 1

              Is there as way to do some sort of query on query, like sub query in SQL

               

              e.g. select * from (select * from Table where path=a or path=b) order by lastModified desc limit 10

              • 4. Re: Query Performance Optimization
                Jörg Hoh Adobe Employee

                Check the JCR-2 specification on http://www.day.com/specs/jcr/2.0/index.html (chapter 6 "Query"). Sub-Queries are not part of the specification.

                 

                Jörg

                • 5. Re: Query Performance Optimization
                  Adnan_N Level 1

                  Did a little more digging and now it seems that using order by on subnode was causing most of the delay i.e. orderby=@jcr:content/jcr:lastModified

                   

                  Once I changed it to orderby=@jcr:created query came down to 0.3 seconds .. very resonable time as compared to 15 secs. From this I am deducing that orderby applied on subnode create query performance issues (can anybody confirm?).

                   

                  I changed my final query to be:

                   

                  nodename=metadata

                  orderby=@jcr:lastModified

                  orderby.index=true

                  orderby.sort=desc

                  p.offset=0

                  p.limit=10

                  group.p.or=true

                  group.0_path=/content/dam/node1

                  group.1_path=/content/dam/node2

                   

                  This is giving about 0.20 - 0.30 sec response time.

                   

                  Please let me know if this approach is good for this case?

                   

                  In general are there any set of tools/best practices one should be using to optimize JCR queries?

                   

                  Thanks,

                  • 6. Re: Query Performance Optimization
                    Jörg Hoh Adobe Employee

                    Does the query really deliver the same result?

                     

                     

                    Jörg

                    • 7. Re: Query Performance Optimization
                      Adnan_N Level 1

                      Both queries are returning different node on same Asset. First query gives me Asset but the second gives me metadata node but metadata node is also enough information for me to work with (since I need Title, description etc)

                      • 8. Re: Query Performance Optimization
                        Jörg Hoh Adobe Employee

                        Ok, sounds reasonable. But then you changed much more than just the orderby statements, therefor your conclusion is a bit misleading. So instead of analyzing a complex structure (the dam:Asset) you do a simple node and property lookup. I am not really familiar with the internals of the mapping of JCR queries to lucene queries, but that can be faster.

                         

                        Jörg

                        • 9. Re: Query Performance Optimization
                          Adnan_N Level 1

                          Oh Sorry, that what you were asking....anyway following query returns Asset nodes and takes about 0.2 secs (on my CQ5 instance)

                           

                          type=dam:Asset

                          orderby=@jcr:created

                          orderby.index=true

                          orderby.sort=desc

                          p.offset=0

                          p.limit=10

                          group.p.or=true

                          group.0_path=/content/dam/node1

                          group.1_path=/content/dam/node2

                           

                          I could be wrong but evidence is pointing towards orderby applied on subnode

                           

                          The reason I used different query for my solution as dam:Asset node doesnt have jcr:lastModified as a direct property.

                           

                          Thanks

                          • 10. Re: Query Performance Optimization
                            Jörg Hoh Adobe Employee

                            Before you decide which one to choose: If you already have a large production system, execute the queries there. The runtime performance there is probably much different from your local development instance.

                             

                            Jörg

                            • 11. Re: Query Performance Optimization
                              Adnan_N Level 1

                              Thanks Jorg for the tip.. All the timestamps I quoted earlier are for from a QA environment which mirrors production.

                               

                              You are right on my local environment none of these queries and taking more that a sec