1. Home
  2. Portal
  3. More than 10000 DB Queries when calling UserGroupRoleLocalServiceUtil

More than 10000 DB Queries when calling UserGroupRoleLocalServiceUtil

Jamie Sammons, modified 3 Years ago.
More than 10000 DB Queries when calling UserGroupRoleLocalServiceUtil
New Member Posts: 23 Join Date: 2/27/12 Recent Posts

Helo,

we observed on our installations (that uses Liferay_6.2.5 GA6), that whenever we do the following call​​​​​

UserGroupRoleLocalServiceUtil.getUserGroupRoles(0, MAX);

and we have more than 10.000 entries in the table UserGroupRole, that after executing the findAll, that for each entry a separate DB-Query will be done to get the most recent UserGroupRole entry (although the whole data is still there).

​​​​​​​After further analyzation, we found out, that the FinderUtil for UserGroupRole was the reason for that. This concrete Finder uses the default cache configuration, that is using RAM only with a max of 10.000 entries.

​​​​​​​We also found a temporary Workaround, using the following property entry in portal-ext.properties

value.object.finder.cache.enabled.com.liferay.portal.model.UserGroupRole=false

Is this the best solution or is there a really good one?
​​​​​​​Why is a FinderUtil for UserGroupRole entities needed, as the update for that entries use a query containing (roleId, groupId & userID) and will return the same data (roleId, groupId & userID)?

Kind Regards,

Philipp

thumbnail
Olaf Kock, modified 3 Years ago.
RE: More than 10000 DB Queries when calling UserGroupRoleLocalServiceUtil
Liferay Legend Posts: 6441 Join Date: 9/23/08 Recent Posts

You have "more than 10.000 entries" and a cache with "10.000" entries, and you write code that loads all "more than 10.000 entries" at once. Naturally, when the last entity is loaded into memory and cache, the first ones will be expired from cache.

You can

  • configure the cache to be sufficient for the number of entities you read at once
  • change your implementation to not​​​​​​​ load all entities at once, but go in 1000's chunks.

As you're on GA6, I hope you have the patch installed, but it's also a good idea to upgrade and check if you can reproduce on the latest version

Philipp Glatzl, modified 3 Years ago.
RE: RE: More than 10000 DB Queries when calling UserGroupRoleLocalServiceUt
New Member Posts: 23 Join Date: 2/27/12 Recent Posts

Thanks for the reply. We are currently working on migrating to Liferay 7, but we will not be that fast. So we would definitly need to fix that also on Liferay 6.

 

Okay, but isnt the FinderCache for entities of UserGroupRole useless?
​​​​​​​It requests an entry with all details and gets the same details back.

 

So is disabling the FinderCache for this entites the best option or are there better options?

 

Thanks,

Philipp

Philipp Glatzl, modified 3 Years ago.
RE: RE: More than 10000 DB Queries when calling UserGroupRoleLocalServiceUt
New Member Posts: 23 Join Date: 2/27/12 Recent Posts

Would be cool to get an answer