[gatein-issues] [JBoss JIRA] Updated: (GTNPORTAL-1880) Performance issue when showing OrganizationManagementPortlet when there are many users in IDM DB

Performance issue when showing OrganizationManagementPortlet when there are many users in IDM DB ------------------------------------------------------------------------------------------------ Key: GTNPORTAL-1880 URL: https://issues.jboss.org/browse/GTNPORTAL-1880 Project: GateIn Portal Issue Type: Bug Security Level: Public(Everyone can see) Components: Identity integration, Performance Affects Versions: 3.1.0-GA Environment: EPP 5.1.0.GA, Picketlink 1.1.7.GA Reporter: Marek Posolda Assignee: Boleslaw Dawidowicz Fix For: 3.2.0-GA Attachments: threadDump1-loadOfAttributes.txt, threadDump2-loadOfAttributeValues.txt Time for load of page with organizationManagementPortlet depends on number of users. When there are very big number of users in picketlink DB (100000 or more) then loading of page can be few minutes. Steps to reproduce: 1) Add many users into IDM database 2) Login as john 3) Go to page http://localhost:8080/portal/private/classic/organization/management When looking into the source of problem, I've found that Hibernate is using 2 very expensive SQL select commands, which needs to load attributes and attribute values of ALL users in DB inspite of the fact that there are only first 10 users shown in the table in UI. Expensive select commands are: 1) Select for loading names of all attributes of all users ( This is called from HibernateIdentityStoreImpl.getAttributes ) select attributes0_.IDENTITY_OBJECT_ID as IDENTITY2_4_1_, attributes0_.ATTRIBUTE_ID as ATTRIBUTE1_1_, attributes0_.ATTRIBUTE_ID as ATTRIBUTE1_9_0_, attributes0_.IDENTITY_OBJECT_ID as IDENTITY2_9_0_, attributes0_.NAME as NAME9_0_, attributes0_.ATTRIBUTE_TYPE as ATTRIBUTE4_9_0_, attributes0_.BIN_VALUE_ID as BIN5_9_0_ from jbid_io_attr attributes0_ where attributes0_.IDENTITY_OBJECT_ID in (select this_.ID from jbid_io this_ inner join jbid_io_type type2_ on this_.IDENTITY_TYPE=type2_.ID inner join jbid_realm rm1_ on this_.REALM=rm1_.ID where rm1_.NAME='idm_realm' and type2_.NAME='USER' and this_.NAME like '%' ) 2) Select for loading text values of all users ( This is called from AttributesManagerImpl.convertAttribute ) select textvalues0_.TEXT_ATTR_VALUE_ID as TEXT1_9_0_, textvalues0_.ATTR_VALUE as ATTR2_0_ from jbid_io_attr_text_values textvalues0_ where textvalues0_.TEXT_ATTR_VALUE_ID in (select attributes0_.ATTRIBUTE_ID from jbid_io_attr attributes0_ where attributes0_.IDENTITY_OBJECT_ID in (select this_.ID from jbid_io this_ inner join jbid_io_type type2_ on this_.IDENTITY_TYPE=type2_.ID inner join jbid_realm rm1_ on this_.REALM=rm1_.ID where rm1_.NAME='idm_realm' and type2_.NAME='USER' and this_.NAME like '%' )) Problem with both select commands is that they need to load attributes of all users in DB even if only attributes for some very limited number of users are really needed. Big problem seems to be storing of query results into Hibernate Query Cache. I am attaching part of thread dumps where can be seen which parts are calling SQL commands. I guess fix can be some change in Hibernate mapping files for HibernateIdentityObject and HibernateIdentityObjectAttribute.