When a Person has as a PersonRole in which the PersonPermission for the Person ClassKind is set to READ doing a GET on SiteDirectory has poor performance

[samatstariongroup]

Prerequisites

• [x] I have written a descriptive issue title
• [x] I have verified that I am running the latest version of the COMET Web Services
• [x] I have searched open and closed issues to ensure it has not already been reported

Description

In the case when there are many EngineeringModels available in a server and when each model has multiple Particpants, a READ on SiteDirectory has poor performance in the following case:

• When a Person has as a PersonRole in which the PersonPermission for the Person ClassKind is set to READ doing a GET on SiteDirectory has poor performance

Steps to Reproduce

• setup a server with the following
  • create a PersonRole called read that has for ClassKind.Person -> READ
  • create a PersonRole called modify that has for ClassKind.Person -> MODIFY
  • create a PersonRole called modifyownperson that has for ClassKind.Person -> MODIFY_OWN_PERSON
  • create 30 users with a distribution of the before mentioned personroles (10 per kind of PersonRole)
  • create 20 models and 30 participants in each model
• Verify that performance of HTTP GET of the SiteDirectory?extent=deep has varying performance depending on the assigned rol

System Configuration

COMET Web Services version: 7.1.1

• [x] PostrgreSQL: 12.3

[antoineatstariongroup]

First investigation with the 2 defined configurations :

• new = rheagroup/cdp4-database-community-edition:latest and rheagroup/comet-webservices-community-edition:8.0.0-rc25
• old = rheagroup/cdp4-database-community-edition:2.0.0 and rheagroup/cdp4-services-community-edition:7.1.1

Associated dumps are contained in the following archive: (each db have setup explained in the issue but instead of having 20 models, there are 30 models)

cdp4db_dumps.zip

Here are results :

new_test_result.txt old_test_result.txt

It seams the we do have a less performant login with the role MODIFY_OWN_PERSON but performance are way better with version 8.0.0-rc25

[antoineatstariongroup]

Investigation: new information;

The PermissionService for READ action, at SiteDirectory level can hit the database on multiple cases: if the PersonAccessRightKind is READ_IF_PARTICIPANT (not on the current scope) or MODIFY_OWN_PERSON.

On that last case, for each Person that is retrieved on the database, a new check is done against the database to check whether the person is a participant within a model that the logged person is also a participant.

[samatstariongroup]

Investigation: new information;

The PermissionService, at SiteDirectory level can hit the database on multiple cases: if the PersonAccessRightKind is READ_IF_PARTICIPANT (not on the current scope) or MODIFY_OWN_PERSON.

On that last case, for each Person that is retrieved on the database, a new check is done against the database to check whether the person is a participant within a model that the logged person is also a participant.

@antoineatrhea that was my suspicion, i think the service is already registered as a per-request, so we can apply some kind of caching routinel Or we can do a more complex, less naive SQL statement in a DAO class to suit the needs and make it more performant.

[antoineatstariongroup]

@samatrhea making BULK check would be more efficient : instead of checking one instance of each type, why not checking all instances of each type directly ? only one SQL statement would then be made (without any changes on current statement), just need to re-think the way that permission and filtering are done.