Performance issue 1.2.2 version

[gabrielemilan]

Hello,

I have a performance issue on version 1.2.2.

I tried to add 200 users and when I try to get all users it takes 30+ seconds to get all users. I profiled the query and it seems there is an issue on some JOIN.

Then I tried with only one user and the result is:

For only one user the query returns 792 rows.

Can you help me on that?

[gabrielemilan]

I tried new version 1.2.3, and the behaviour is different.

Now I have a lot of simple queries, it seems one for each property:

And also in this case, the get of users is very slow.

[simpleidserver]

Hello,

I'm going to take a look to the performance issue. I think there is a problem when the SCIMAPI is trying to fetch users with their roles.

[simpleidserver]

Hello,

The performance issue should be fixed in the branch "release/1.2.3".

In the previous implementation, for each user the groups and entitlements were fetched.

Now in the new implementation, those information are inserted in the table by the update operations like : HTTP PUT, HTTP PATCH and HTTP POST. Because those information don't exist in your database, I create a draft migration script which can be executed in order to insert "groups" & "entitlements" properties inside the User SCIM Representation. The migration script can be found here : https://github.com/simpleidserver/SimpleIdServer/blob/release/1.2.3/samples/SqlScripts/UpdateMembers.sql

Note : The migration script is a draft and is working on my machine.

Can-you please check if you still observed performance problem ?

Steps

• If needed execute the SQL script in order to insert missing properties like "groups" and "entitlements"
• Run the API and check the result.

[gabrielemilan]

Hello, I tried the last version, it seems that there is the issue still:

With only one user it takes 10 sec. I see in the console log, that there are a lot of queries to db:

https://gifyu.com/image/O4St

[simpleidserver]

Il could reproduce your issue. I'm going to make some modifications in the Database schema in order to improve the query performance.

Sorry again for the inconvenience.

[simpleidserver]

Hello,

I made some changes in the branch "2.0.0". The Database schema has been updated in order to fix the performance issue :) The performance is much better ! Now it take "233ms" to fetch "189" users and the sort operation is also faster. I'm going to fix the MongoDB project, can-you please check if everything is working on your side ?

Kind regards,

SimpleIdServer

[gabrielemilan]

Hello,

it seems ok now, good job :-).

I saw that you remove a table from database, I have to upgrade from 1.2.2 to 2.0.0, do you have a script to do the data rollout?

I saw also that the solution is not building, but I think you are on it :-).

Thanks a lot.

[simpleidserver]

Hello,

The solution is not building because I still have to work on the Cassandra project :'(. When I have some time I'm also going to work on a migration script !

Kind regards,

SimpleIdServer

[simpleidserver]

SQL Migration scripts have been added in the branch "release/2.0.0". Please follow the following procedure in order to migrate the data from version 1.2.2 to version 2.0.0.

Navigate to the folder : https://github.com/simpleidserver/SimpleIdServer/tree/release/2.0.0/Migrations/SCIM/1.2.2To2.0.0 and execute the SQL Scripts in the correct order:

1. 1-EraseMembers.sql : Remove members attributes from User representation
2. 2-UpdateMembers.sql : Add members attributes into User representation.
3. 3-ShowAdvanced : Turns on advanced options
4. 4-EnabledCmdShell.sql : Turns on xp_cmdshell
5. 5-CreateTable.sql : Create tables and update columns.
6. 6-CreateFunction.sql : Create function used to build the full path.
7. 7-MigrateData.sql : Update the SCIM Representation attributes and SCIM Schema Attributes
8. 8-Clean.sql : Remove temp tables
9. Execute the project "SimpleIdServer.Scim.SqlServer.Startup"

[simpleidserver]

The SCIM MongoDB project has been updated & the solution is building in the branch "release/2.0.0"