[SCIM] Performance issues version 4.0.5

[polybogdan]

Hello,

I'm facing some performance issues when doing GET groups and each group has around 100 members and the number of total groups are around 1000.

Bellow some scenarios:

1. When passing the sorting parameters the query results are received in 1 minute and 30 seconds

2. Without the sorting parameters the query results are received in 30 seconds that is still too much

3. I saw that there is an option to pass the excludedAttributes where we set the "members" and the query results are 20 seconds that is still too much

Is there something that can be improved on the library side ?

Regards

Bogdan

[simpleidserver]

I made some modifications in the master branch to enhance performance during querying. There was an issue with the following code:

IQueryable<EnrichedRepresentation> representationAttributes = from a in _scimDbContext.SCIMRepresentationLst.AsQueryable()
    join b in _scimDbContext.SCIMRepresentationAttributeLst.AsQueryable() on a.Id equals b.RepresentationId into FlatAttributes
    select new EnrichedRepresentation
    {
        Id = a.Id,
        ExternalId = a.ExternalId,
        Version = a.Version,
        Representation = a,
        FlatAttributes = FlatAttributes
    };

The Aggregate executed too many $lookup operations, causing a performance problem. To address this issue, the algorithm's order has been changed as follows:

• Filter the representations.
• Sort the representations.
• Paginate the representations.
• Load the attributes of the representations.

Could you please clone the master branch and check if the performance is improved with these changes?

[polybogdan]

Hello,

Thanks for the quick fix. I'm testing the version and will get back to you with the feedback.

Regards

[polybogdan]

Hello again,

Summing up the test results for each of the initial described scenario.

1. When passing the sorting parameters the query results were received in 1 minute and 30 seconds Now the query results are received in around 20 seconds ( big improvement )

2. Without the sorting parameters the query results were received in 30 seconds The tests result for this one are a bit random. Something e the query results are received in around 30 seconds ( like before ) and sometimes they are received in 12 seconds

3. Passing the excludedAttributes where we set the "members" and the query results were 20 seconds The tests result for this one are a bit random. Something e the query results are received in around 30 seconds ( slower then before ) and sometimes they are received in 10 seconds ( a bit better )

Additionally i noticed that the get Users has some issues:

1. /Users?sortBy=name.familyName&sortOrder=ascending does not sort
2. /Users?filter=groups[display eq "GroupName"] is not working and previously it was

Let me know if there is something else that can be done

Regards

Bogdan

[simpleidserver]

I made some modifications in the master branch to improve performance with MongoDB.

The issues you mentioned have also been fixed. Could you please fetch the latest changes and try again?

[polybogdan]

Hello

did some tests and for all three scenarios the response time is 10 seconds that is acceptable.

For the issues:

1. /Users?sortBy=name.familyName&sortOrder=ascending Now is working partially. The above query works but if we add a filter by whatever field the Resources array is empty ( totalResults are correct )

2. /Users?filter=groups[display eq "GroupName"] Still not working

Adding another point

3. The search by id results in server error /Users?filter=id eq "66afdcc9-93b3-446d-a875-8f9f07b5e281"

[simpleidserver]

The following issues have been fixed in themaster branch :

• /Users?filter=groups[display eq "GroupName"]
• /Users?filter=id eq "66afdcc9-93b3-446d-a875-8f9f07b5e281"

Regarding the HTTP request /Users?sortBy=name.familyName&sortOrder=ascending&filter=name[givenName co "Bodgan"], I attempted to reproduce the problem on my local machine without success.

Could you please check in the representations MongoDB collection if the representation that should be returned by the HTTP request contains a resource type equal to 'Users'?

Additionally, could you check if the HTTP request /Users?sortBy=name.familyName&sortOrder=ascending&filter=name[givenName co "Bodgan"] returns any results?

[polybogdan]

Going to check asap the latest changes.

For the last one /Users?sortBy=name.familyName&sortOrder=ascending&filter=name[givenName co "Bodgan"]

In fact is acting strangely /Users?sortOrder=ascending&sortBy=name.familyName&filter=name[givenName co "Bogdan"] returns totalResults 2 ( that is correct and in the Resources array only one item

changing the sortBy to "name.givenName" it doesnt /Users?sortOrder=ascending&sortBy=name.givenName&filter=name[givenName co "Bogdan"]

event the following doesnt work /Users?sortOrder=ascending&sortBy=name.familyName&filter=emails[value eq "bogdan.poliec@mail.com"]

The response body reflect the result seen in postman

By this : Could you please check in the representations MongoDB collection if the representation that should be returned by the HTTP request contains a resource type equal to 'Users'? You were expecting this check ?

Keep in mind that all the filters work as "stand alone" in combination with the sorting parameters they behave in a strange way

[polybogdan]

After last pull from master :

• /Users?filter=groups[display eq "GroupName"] is ok
• /Users?filter=id eq "d153b387-b832-4b32-b78e-5c0ebe91ead3" results in server error
• GET Groups are again slow

-Similar to the GET users issue has ( paginng and filter parameters do not return results ) /Groups?startIndex=101&count=100&filter=displayName eq "EURO 2024-FO_Broadcaster"

[simpleidserver]

I believe I have identified the issue with the sortBy operation and the exception "Value cannot be null" Could you please pull the latest changes from the master branch and try again?

Regarding the performance issue, could you please check which part of the "FindSCIMRepresentations" function is consuming time. https://github.com/simpleidserver/SimpleIdServer/blob/2ae17b2c389cddb1899b245371ffdd9520c2fc7a/src/Scim/SimpleIdServer.Scim.Persistence.MongoDB/SCIMRepresentationQueryRepository.cs#L61

I conducted two performance tests:

Loaded 100 groups and added 100 members to each group. The operation "HTTP GET /Groups" took 1.6 seconds to execute.

Loaded 200 groups and added 100 members to each group. The operation "HTTP GET /Groups" took 1.63 seconds to execute.

I did not observe a difference between the two executions

[polybogdan]

Hello,

Sorry for the late reply.

• /Users?filter=id eq "d153b387-b832-4b32-b78e-5c0ebe91ead3" - filter by id is ok now
• /Groups?sortBy=displayName&sortOrder=ascending&startIndex=101&count=100&filter=displayName co "UCL" does not return the results i noticed that the issue is related somehow to the sorting inside FindSCIMRepresentations commenting out the row 126 the results are returned ( not sorted tho )
• Related to the performance issues of the GET Groups on a local database with around 4000 users, 700 groups, 900 entitlements and each group with around 100 members some members with around 80 groups entitlements with 200 members the response is generally between 3 and 5 seconds

yet pointing to the mongo db of any other environment the performances are very poor. I debugged the FindSCIMRepresentations and most of the time spent is the actual query to the database.

at this point i dont know if other performance improvements could be made on the query, besides what you already did, but what i would ask if you can provide some recommended indexes to be applied to the mongo db to speed it up.

[simpleidserver]

Indexes are automatically created by the SCIM server during its startup. Could you please check if the following indexes are created in the SCIMRepresentationAttribute collection?

• RepresentationId_1_SchemaAttributeId_1_ValueString_1
• RepresentationId_1

Code : https://github.com/simpleidserver/SimpleIdServer/blob/997aad167073c19462b5dd48218dd15b41a30408/src/Scim/SimpleIdServer.Scim.Persistence.MongoDB/Extensions/MongoDbClientExtensions.cs#L55

Regarding the sorting issue, I tested it on my local machine, and everything is working fine. I observed that you set the startIndex to 101 in the request. In this case, it is normal for the request to be empty because only 90 results are returned.

[polybogdan]

Hello,

Regarding the sorting issue, you are right the startIndex was the problem, sorry about that. I checked the indexes and they are all defined.

At this point i would kindly ask you to release the latest improvements and if possible the fix done for the SupportTransaction flag related to the issue [SCIM] Error in bulk insert using version 4.0.5

[simpleidserver]

Hello,

The Nuget package 4.0.7-rc1 https://www.nuget.org/packages/SimpleIdServer.Scim is available :)

KR,

SID