Slowly displaying list of users

[henning-gerhardt]

If you have a big list of users, f.e. 300 active and inactive users, displaying this list is significant slower in Kitodo.Production 3.3 in compare to 3.2.1. Maybe adding the possibility to remove assigned tasks of a user did slow down this displaying. As this slow speed effects even other tabs (f.e. roles, ldap group, ldap server) in the "user page" site you need a lot of time if you want to change / adjust f.e many roles.

[solth]

I am not sure how the number of users in the system can have any effect on the loading times of that list - unless you have configured your user settings to show all 300 active users on one page.

[henning-gerhardt]

Table displaying is configured to 100 shown rows as this was the maximum in 2.x and a good value for any large admin task. Why should I reduce this value? I did not understand why this "removing task from user" are displayed in any case as this is only useful in certain scenarios like deleting user or if an user in holidays and is blocking some work. For the last scenario you can - at least in 2.x - use the administration task to set down a task from "in work" to "open" on the process list.

Edit: I don't even did not understand why loading the user list is even triggered on the other tabs on this page. In my opinion the content of each tab should be loaded or updated only if they are used.

[solth]

If you do not want this action to be shown on default because you do not need it, you can always configure the corresponding roles to exclude the authority Aufgaben zurücksetzen. That should skip the rendering of the action icon and the related checks.

[henning-gerhardt]

This is good to know but the lack of documentation where which authority is used and / or how the interaction is where is executed make it hard to decide which authority is really useful and should be used in which role.

But the problem remains: displaying this icon slow down the interaction of this whole page on all tabs of this page.

[henning-gerhardt]

Even after removing this authority from my list of authorities and log out and log in back, the displaying of users is slow :-(

[solth]

Can you be more specific what "slow" means? Seconds? Minutes? And how much of a difference is there between 3.2.1 and 3.3 in your opinion? (I guess you can't provide numbers to support your claim, right?)

[henning-gerhardt]

Loading a user list page in 3.3 with 100 entries takes

• with the assigned right Aufgaben zurücksetzen between 25 and 45 seconds as this depends on how many users has assigned tasks
• without assigned right Aufgaben zurücksetzen displaying the same user page takes between 12 and 17 seconds

I did not have access to a 3.2.x system anymore but there the loading time of this page was between 5 and 10 seconds at least. In comparing with 2.x loading the user list with 100 entries per page takes less then 2 seconds.

[solth]

Thanks for the information. That at least shows that removing that authority does have a significat effect on the loading speed. But I agree, 12 to 17 seconds is still to slow for loading 100 user entries - given you are not using some underpowered laptop or something 😼

Something else that could have had an impact on the loading times between 3.2.1 and 3.3 is the indication of deactivated users in 3.3 (see #4289 )

[henning-gerhardt]

I take timings on our real environment and every used system has no significant cpu usage. So removing the authority improves but it is still slow in comparing to 2.x (2 seconds on 2.x and 12 seconds in 3.x).

[matthias-ronge]

The last release had a higher overall workspeed, to my experience. Is this still a problem?

[Kathrin-Huber]

Is this fixed with the current performance improvements?

[henning-gerhardt]

This was not part of the current performance improvements. Listing the users is still slow even if the authority "Aufgaben zurücksetzen" is removed. So improvement is still needed!

[thomaslow]

I had a look at the user table today (trying to make more columns sortable). I think the problem is related to the way data is queried from the database. There are several information shown in the user list that is not stored in the "user" database table. Because of that, Hibernate generates multiple SQL queries for each user listed in the table.

The following information each trigger a separate SQL query for each user:

• the 3 columns "Rollen", "Mandanten" and "Projekte"
• the styling of the action "Aufgabe zurücksetzen", which triggers a check whether a task is currently in progress for that user (and thus, may be reset)

So, when displaying 100 users, there will be:

• 1 query to find all users
• 100 individual queries to get the column value "Rollen" for each user
• 100 individual queries to get the column value "Mandanten" for each user
• 100 individual queries to get the column value "Projekte" for each user
• 100 individual queries to check whether a user currently has a task in progress

Obviously, this is extremely slow.

As far as I can tell, there is no easy way to fix this with Hibernate. However, SQL databases generally support these kind of queries. For example, the following custom SQL query is able to collect the required information with a single SQL statement:

Unfortunately, the SQL command GROUP_CONCAT is not supported by Hibernate and not universally compatible with all databases (PostgreSQL uses other syntax).

So, in my opinion, there is no easy solution to this. Either:

• accept the slow performance
• deal with database-specific custom SQL queries to improve performance
• show less information in the user table such that the Hibernate performance problem isn't that bad

[henning-gerhardt]

Two thoughts:

1) Should the user view independent of the clients or should this view depending on the client which the user is using? Some parts are client-depending other parts not and for the user view I'm unsure what is the "best" solution.

2) Whats about of using a VIEW inside the database to gather this information together and accessing this VIEW with a separate Hibernate bean? The VIEWmust be written in a such way that the VIEW is working with different database management systems, if this is possible.

[thomaslow]

Regarding point 2. It is possible to map a database VIEW or custom SQL query to a Hibernate bean. Unfortunately, if the VIEW uses database-specific SQL statements, it is also not compatible with other databases.

Since Hibernate knows what database is currently used, it would be technically possible to implement database-specific queries using simple if-statements (if db=Mysql use this query, if db=Postgres use this query). It should also be possible to add a fallback query for unusual databases that will then use the universally compatible Hibernate-query with slow performance. Unfortunately, these database-specific queries could not be easily tested with integration tests, though.