Skip to content

Checking Related User Performing Large Number of Queries #165

@kkoz

Description

@kkoz

When attempting to retrieve data about a particular user, certain fields of the Experimenter model are protected as "PrivilegedStrings"
https://github.com/ome/omero-model/blob/c6a8c91fc8114307989b4f85d9b4917d679303c7/src/main/resources/templates/object.vm#L1458C7-L1458C7
This filtering is implemented in omero-server in the PropertyFilterInitializer class

PrivilegedStringTypeDescriptor.setFilter(PrivilegedStringTypeDescriptor.Filter.RELATED_USER, this::isRelatedUser);

The main issue with this is that the implementation is such that when a user who isn't an admin or group owner needs to check if a different user is "related", a SQL query is executed for each PrivilegedString field, which is this case is 6 fields, resulting in 6*(# of users) queries being executed when doing these checks
final PreparedStatement statement = connection.prepareStatement(
"SELECT 1 FROM GroupExperimenterMap m1 WHERE m1.child = ? AND m1.parent <> ? AND EXISTS " +
"(SELECT 1 FROM GroupExperimenterMap m2 WHERE m2.child = ? AND m1.parent = m2.parent) AND EXISTS " +
"(SELECT 1 FROM ExperimenterGroup g WHERE m1.parent = g.id AND g.permissions & 64 = 64)");

When the number of users and/or groups is large, this can result in performance issues.

Metadata

Metadata

Assignees

Labels

No labels
No labels

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions