Uploaded image for project: 'Magnolia'
  1. Magnolia
  2. MAGNOLIA-8347

JcrQueryBuilder performance issues.

    XMLWordPrintable

Details

    • Bug
    • Resolution: Obsolete
    • High
    • None
    • 6.2.17
    • None
    • None

    Description

      Performance of the column search in the pages and assets app when we tested it on our large repository (100.000+ nodes) was not really usable. Analysis showed that the queries generated by JcrQueryBuilder did not really perform.

      By experimenting the biggest improvement was to reduce the use of likes, and if used only with a postfix wildcard (ie 'test%' instead of '%test%') as this reduces the number of hits we included an contains query as well with little to no performance hit (The resulting query is quite similar to what Magnolia 5 used in the search view).

      Another thing that increased performance to remove the use of wildcards on columns that have limited values (ie mgnl:template and such).

      A curious one (which we fully blame on JackRabbit) was the the jcrName wildcard query (ie '%nodename%). A comparison with the queries generated by Magnolia 5 revealed that adding a property existence query increased performance significantly. So the difference between one without and one without on our repository is really something:
      SELECT * FROM [nt:base] AS t WHERE ISDESCENDANTNODE(t, [/]) AND (t.[jcr:primaryType] = 'mgnl:page' OR t.[jcr:primaryType] = 'mgnl:variants' OR t.[jcr:primaryType] = 'mgnl:variant') AND (LOWER(LOCALNAME(t)) LIKE '%test%' )
      546 nodes returned in 19210ms
      SELECT * FROM [nt:base] AS t WHERE ISDESCENDANTNODE(t, [/]) AND (t.[jcr:primaryType] = 'mgnl:page' OR t.[jcr:primaryType] = 'mgnl:variants' OR t.[jcr:primaryType] = 'mgnl:variant') AND (LOWER(LOCALNAME(t)) LIKE '%test%' OR t.['xxxx'] is not null)
      546 nodes returned in 426ms

      Checklists

        Acceptance criteria

        Attachments

          Issue Links

            Activity

              People

                dmaslanka Dominik Maslanka
                rico.jansen Rico Jansen
                Nucleus
                Votes:
                1 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                  Created:
                  Updated:
                  Resolved:
                  Work Started:

                  Checklists

                    Bug DoR
                    Task DoD