[gatein-issues] [JBoss JIRA] Created: (GTNPORTAL-1811) Bad performance of query for get all Pages when many users in database

Bad performance of query for get all Pages when many users in database ---------------------------------------------------------------------- Key: GTNPORTAL-1811 URL: https://issues.jboss.org/browse/GTNPORTAL-1811 Project: GateIn Portal Issue Type: Bug Security Level: Public (Everyone can see) Components: Performance Affects Versions: 3.1.0-GA Reporter: Marek Posolda Fix For: 3.2.0-GA This performance bottleneck can be seen when there are many users in database (I tried with 2100 and 5000). It can be simulated by doing one of these things: * login as "root" and go to page http://localhost:8080/portal/private/classic/administration/pageManagement * login as "root" and go to page http://localhost:8080/portal/private/classic/portalnavigation -> click to "Edit navigation" of some portal -> Click "Add node" Both of these are showing UIPageBrowser and calling method UIPageBrowser.feedDataWithQuery(Query<Page> query) which creates and execute Query for load all Page instances of all owners and owner types: Query<Page>(null, null, null, null, Page.class) Execution of this query is internally running this JCR query: SELECT * FROM mop:page WHERE jcr:path LIKE '/production/mop:workspace/%/%/mop:rootpage/mop:children/mop:pages/mop:children/%' And this is the cause of bottleneck. Running of this JCR query took me 55 seconds when running for first time and around 30 seconds for second and other times (I am using MySQL 5.1 as database). And I have 2100 users in portal, which means that node "/production/mop:workspace/mop:usersites" has 2100 subnodes. With 5000 users, I have 126 seconds for first execution of query and around 45 seconds for next executions. Good thing is that UIPageBrowser is shown only from admin portlets and so bottleneck should not affect normal portal users... I am attaching part of server log in file pageQueryBottleneck-log.txt (Enabled DEBUG logging for "org.exoplatform.portal.pom.config.ExecutorDispatcher" and "exo.jcr.component.core.QueryImpl" ) -- This message is automatically generated by JIRA. For more information on JIRA, see: http://www.atlassian.com/software/jira