This article is regarding a performance issue that may surface when executing an LDAP import using the UserGroup method for a large amount of users. Specifically, the issue is that the import process could take longer than expected.
The cause of this issue is that the SQL query ("SELECT userId FROM Users_UserGroups WHERE userGroupId = ?" ) will be executed for each user being imported. This query execution could create large result set if there are a large amount of users in the database. Since the Oracle JDBC connection defaultRowPrefetch size is only at 10 rows, the defaultRowPrefetch size creates a bottleneck in terms of fetching the query results.
Resolution
To improve the bulk LDAP import performance, there are two possible options to consider using.
Option 1
Tune the Oracle JDBC connection pool size to set the defaultRowPrefetch value from 10 to a more appropriate number.
Note: Database related tuning is not supported by Liferay Support. Please refer to Oracle Resource for more information.
Option 2
LPS-49427 was raised to address the issue by introducing Cacheless Table Mapper through the optimized query statement. In addition to using the fix from LPS-49427, the propertytable.mapper.cacheless.mapping.table.names={table name} needs to be added to the portal-ext.properties file to configure which table to adopt this option.To use this property, you would put the names of the tables and separate them by commas.
For example: table.mapper.cacheless.mapping.table.names=user_,users_user_group
Note: This option does not require any change of JDBC connection pool. The table name for the property is case sensitve.