Slow process template page access #5336

henning-gerhardt · 2022-08-31T06:48:04Z

Describe the bug
The first access of the process template page is slow and there are only 10 process templates to be shown - i guess the time for this is 30 seconds. I don't know if the time depends on how many projects and indirect processes are assigned to a process template or which other reason is responsible to this slow displaying.
If you later - or in the same login session - open the process template page again, then you get the results a lot faster. After you logged out and logged in again the display speed is on the first access slow again.

To Reproduce
Steps to reproduce the behavior:

Create some process templates and assign them to processes
Logout / Login
Open the process template page
Wait a long time to see all process templates

Expected behavior
Displaying of process templates should not take "ages" to get displayed.

Release
3.4.4-SNAPSHOT

BartChris · 2022-09-07T09:00:41Z

It seems like the function isTemplatUsed is called four times for every template displayed in the templateList-xhtml, e.g.

kitodo-production/Kitodo/src/main/webapp/WEB-INF/templates/includes/projects/templateList.xhtml

Line 84 in f58592b

    
           rendered="#{TemplateForm.isTemplateUsed(item.id) or (SecurityAccessController.hasAuthorityToViewTemplate() and not SecurityAccessController.hasAuthorityToEditTemplate())}">

kitodo-production/Kitodo/src/main/webapp/WEB-INF/templates/includes/projects/templateList.xhtml

Line 107 in f58592b

    
           title="#{TemplateForm.isTemplateUsed(item.id) ? msgs.cannotDeleteTemplate : msgs.delete}">

Which would probably mean 40 database (Elasticsearch?) queries in your case:

kitodo-production/Kitodo/src/main/java/org/kitodo/production/forms/TemplateForm.java

Line 414 in f58592b

    
           return !ServiceManager.getProcessService().findByTemplate(templateId).isEmpty();

henning-gerhardt · 2022-09-07T12:58:57Z

Thank you for your research, @BartChris . I can more or less confirm that the call in TemplateForm class is causing a lot of delay while displaying the result list. Maybe is using ElasticSearch for such kind of queries not a good solution as first all search results (or at least of a maximum of 10000) are returned back and transformed into Java objects and then only to decide is the returned list empty or not. If ElasticSearch must be used then should the result should be reused in the xhtml file instead of starting the query over and over again but reusing the result should be considered in any case independent where the search is done.

BartChris · 2022-09-07T14:16:37Z

I am wondering if this flag ("is_used_by_processes") should just be a property of the template, set to NULL for existing templates. Then this has to be only calculated once and then stored in the database. Whenever a process is deleted it could be checked, if the template is still referenced by a process and the property could be changed if necessary.
This would duplicate information which is already contained in the database, but it would speed up the use case.

henning-gerhardt · 2022-09-07T14:22:40Z

If getting this information from the database (template is used by processes or not) instead of ElasticSearch you did not need such a field as this information can be retrieved through a good query (left or right join, depending on which side you start the query) to the database - which is possible through Hibernate as used database layer too.

matthias-ronge · 2024-11-26T16:56:23Z

I just came over this again and saw that it is loading all processes for each comment or batch. But I don’t understand why.

MariaDB log while waiting for the process template page to appear:

222 Query	select comments0_.process_id as process10_6_0_, comments0_.id as id1_6_0_, comments0_.id as id1_6_1_, comments0_.user_id as user_id7_6_1_, comments0_.isCorrected as iscorrec2_6_1_, comments0_.correctionDate as correcti3_6_1_, comments0_.correctionTask_id as correcti8_6_1_, comments0_.creationDate as creation4_6_1_, comments0_.currentTask_id as currentt9_6_1_, comments0_.message as message5_6_1_, comments0_.process_id as process10_6_1_, comments0_.type as type6_6_1_ from comment comments0_
    where comments0_.process_id=3208
222 Query	rollback
222 Query	SET autocommit=1
213 Query	SET autocommit=0
213 Query	select batches0_.process_id as process_2_2_0_, batches0_.batch_id as batch_id1_2_0_, batch1_.id as id1_1_1_, batch1_.title as title2_1_1_, batch1_.type as type3_1_1_ from batch_x_process batches0_ inner join batch batch1_ on batches0_.batch_id=batch1_.id
    where batches0_.process_id=3208
213 Query	rollback
213 Query	SET autocommit=1
222 Query	SET autocommit=0
222 Query	select comments0_.process_id as process10_6_0_, comments0_.id as id1_6_0_, comments0_.id as id1_6_1_, comments0_.user_id as user_id7_6_1_, comments0_.isCorrected as iscorrec2_6_1_, comments0_.correctionDate as correcti3_6_1_, comments0_.correctionTask_id as correcti8_6_1_, comments0_.creationDate as creation4_6_1_, comments0_.currentTask_id as currentt9_6_1_, comments0_.message as message5_6_1_, comments0_.process_id as process10_6_1_, comments0_.type as type6_6_1_ from comment comments0_
    where comments0_.process_id=3207
222 Query	rollback
222 Query	SET autocommit=1
213 Query	SET autocommit=0
213 Query	select batches0_.process_id as process_2_2_0_, batches0_.batch_id as batch_id1_2_0_, batch1_.id as id1_1_1_, batch1_.title as title2_1_1_, batch1_.type as type3_1_1_ from batch_x_process batches0_ inner join batch batch1_ on batches0_.batch_id=batch1_.id
    where batches0_.process_id=3207
213 Query	rollback
213 Query	SET autocommit=1
222 Query	SET autocommit=0
222 Query	select comments0_.process_id as process10_6_0_, comments0_.id as id1_6_0_, comments0_.id as id1_6_1_, comments0_.user_id as user_id7_6_1_, comments0_.isCorrected as iscorrec2_6_1_, comments0_.correctionDate as correcti3_6_1_, comments0_.correctionTask_id as correcti8_6_1_, comments0_.creationDate as creation4_6_1_, comments0_.currentTask_id as currentt9_6_1_, comments0_.message as message5_6_1_, comments0_.process_id as process10_6_1_, comments0_.type as type6_6_1_ from comment comments0_
    where comments0_.process_id=3206
222 Query	rollback

BartChris · 2024-11-26T17:17:09Z

Can we leverage HQL here to just get a map with one query which indicates wether a process template is used or not or the count how many times the template is used (If it is not used, it is not in the list)? And store that in the backing bean to not having to recalculate it. If i am not mistaken both things can also be expressed as HQL Query.

SELECT t.id AS template_id, 
       CASE WHEN EXISTS (
           SELECT 1 FROM process p WHERE p.template_id = t.id
       ) THEN 1 ELSE 0 END AS is_used
FROM template t;

gives

template_id | is_used
4	1
6	0
7	1
12	1
13	1
10	1
11	1
14	1
15	1

SELECT template_id, count(*) from process p group by template_id  ;

gives

template_id | count
4	4
7	945
10	11
11	3
12	49
13	47
14	1269
15	7

henning-gerhardt added the bug label Aug 31, 2022

BartChris linked a pull request Jan 23, 2025 that will close this issue

Speed up process template page #6377

Draft

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Slow process template page access #5336

Slow process template page access #5336

henning-gerhardt commented Aug 31, 2022

BartChris commented Sep 7, 2022 •

edited

Loading

henning-gerhardt commented Sep 7, 2022 •

edited

Loading

BartChris commented Sep 7, 2022 •

edited

Loading

henning-gerhardt commented Sep 7, 2022

matthias-ronge commented Nov 26, 2024 •

edited

Loading

BartChris commented Nov 26, 2024 •

edited

Loading

Slow process template page access #5336

Slow process template page access #5336

Comments

henning-gerhardt commented Aug 31, 2022

BartChris commented Sep 7, 2022 • edited Loading

henning-gerhardt commented Sep 7, 2022 • edited Loading

BartChris commented Sep 7, 2022 • edited Loading

henning-gerhardt commented Sep 7, 2022

matthias-ronge commented Nov 26, 2024 • edited Loading

BartChris commented Nov 26, 2024 • edited Loading

BartChris commented Sep 7, 2022 •

edited

Loading

henning-gerhardt commented Sep 7, 2022 •

edited

Loading

BartChris commented Sep 7, 2022 •

edited

Loading

matthias-ronge commented Nov 26, 2024 •

edited

Loading

BartChris commented Nov 26, 2024 •

edited

Loading