Response time when querying assets is proportional to the number of roles a user possesses
Issue: In our production system our LTI users have upwards of 30 roles, resulting in response times of at least 15 seconds whenever an event's metadata/scheduling information is updated or conflict checks are performed.
For non-privileged users, roles determine whether a user has read/write access to an asset. This determination takes place within the AssetManagerWithSecurity class, via a database/persistence query which considers the underlying asset and all of the (requesting) user's roles.
Currently, all the roles possessed by a user are used to construct the query above; this results in the query blow-out described by MH-12641.
Attached is a snapshot of a New Relic event summary of the latest conflict check performed by an end-user, where the response time is 39.8s.
Ideally, Asset Manager should filter out those roles not relevant to the requested asset before performing the DB query.
It would be great if roles not relevant to the mediapackage are filtered out before commencing with the DB query.
I was thinking something along the lines of
A single conflict check by an end-user today took 92s to complete. Query below:
The codepath relevant to the query's construction can be found in AssetManagerWithSecurity.java on lines 179 thru 193.
Excuse the format below, but I can't seem to setup formatting on this server: