View Issue Details

IDProjectCategoryView StatusLast Update
0023245mantisbtperformancepublic2019-03-16 20:20
Reportercproensa Assigned Tocproensa  
PrioritynormalSeverityminorReproducibilityhave not tried
Status closedResolutionfixed 
Product Version 
Target Version2.20.0Fixed in Version2.20.0 
Summary0023245: project versions are not cached efficiently
Description

When using view_all_bug_page, and having several projects/subprojects in the filter scope, there are a lot of database queries related to project versions
As an example, the attached log, shows the queries are also repeated.

1) Versions could be fetched in one query for all needed projects in the visualized issues.
2) A project without versions should keep n empty cached value. Otherwise, every lookup is a miss and uses an additional database quey

Additional Information
DB-193  0.0010  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 13, 0, 12, 6 ) AND obsolete = 0 ORDER BY date_order DESC
DB-194  0.0009  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 13, 0, 12, 6 ) AND obsolete = 0 ORDER BY date_order DESC
DB-195  0.0008  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 13, 0, 12, 6 ) AND obsolete = 0 ORDER BY date_order DESC
DB-196  0.0010  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 6, 0 ) AND obsolete = 0 ORDER BY date_order DESC
DB-197  0.0007  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 6, 0 ) AND obsolete = 0 ORDER BY date_order DESC
DB-198  0.0008  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 6, 0 ) AND obsolete = 0 ORDER BY date_order DESC
DB-199  0.0008  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 12, 0, 6 ) AND obsolete = 0 ORDER BY date_order DESC
DB-200  0.0007  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 12, 0, 6 ) AND obsolete = 0 ORDER BY date_order DESC
DB-201  0.0008  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 12, 0, 6 ) AND obsolete = 0 ORDER BY date_order DESC
DB-202  0.0008  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 105, 0, 6 ) AND obsolete = 0 ORDER BY date_order DESC
DB-203  0.0008  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 105, 0, 6 ) AND obsolete = 0 ORDER BY date_order DESC
DB-204  0.0008  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 105, 0, 6 ) AND obsolete = 0 ORDER BY date_order DESC
DB-205  0.0010  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 133, 0, 7 ) AND obsolete = 0 ORDER BY date_order DESC
DB-206  0.0008  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 133, 0, 7 ) AND obsolete = 0 ORDER BY date_order DESC
DB-207  0.0009  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 133, 0, 7 ) AND obsolete = 0 ORDER BY date_order DESC
DB-208  0.0007  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 126, 0, 122 ) AND obsolete = 0 ORDER BY date_order DESC
DB-209  0.0009  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 126, 0, 122 ) AND obsolete = 0 ORDER BY date_order DESC
DB-210  0.0008  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 126, 0, 122 ) AND obsolete = 0 ORDER BY date_order DESC
DB-211  0.0010  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 129, 0, 122 ) AND obsolete = 0 ORDER BY date_order DESC
DB-212  0.0015  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 129, 0, 122 ) AND obsolete = 0 ORDER BY date_order DESC
DB-213  0.0008  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 129, 0, 122 ) AND obsolete = 0 ORDER BY date_order DESC
DB-214  0.0008  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 20, 0, 9 ) AND obsolete = 0 ORDER BY date_order DESC
DB-215  0.0008  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 20, 0, 9 ) AND obsolete = 0 ORDER BY date_order DESC
DB-216  0.0008  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 20, 0, 9 ) AND obsolete = 0 ORDER BY date_order DESC
DB-217  0.0009  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 54, 0, 6 ) AND obsolete = 0 ORDER BY date_order DESC
DB-218  0.0014  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 54, 0, 6 ) AND obsolete = 0 ORDER BY date_order DESC
DB-219  0.0008  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 54, 0, 6 ) AND obsolete = 0 ORDER BY date_order DESC
DB-220  0.0009  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 50, 0, 9 ) AND obsolete = 0 ORDER BY date_order DESC
DB-221  0.0010  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 50, 0, 9 ) AND obsolete = 0 ORDER BY date_order DESC
DB-222  0.0013  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 50, 0, 9 ) AND obsolete = 0 ORDER BY date_order DESC
DB-223  0.0008  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 71, 0, 9 ) AND obsolete = 0 ORDER BY date_order DESC
DB-224  0.0008  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 71, 0, 9 ) AND obsolete = 0 ORDER BY date_order DESC
DB-225  0.0008  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 71, 0, 9 ) AND obsolete = 0 ORDER BY date_order DESC
DB-226  0.0008  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 58, 0, 9 ) AND obsolete = 0 ORDER BY date_order DESC
DB-227  0.0009  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 58, 0, 9 ) AND obsolete = 0 ORDER BY date_order DESC
DB-228  0.0008  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 58, 0, 9 ) AND obsolete = 0 ORDER BY date_order DESC
DB-229  0.0008  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 178, 0 ) AND obsolete = 0 ORDER BY date_order DESC
DB-230  0.0008  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 178, 0 ) AND obsolete = 0 ORDER BY date_order DESC
DB-231  0.0009  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 178, 0 ) AND obsolete = 0 ORDER BY date_order DESC
DB-232  0.0008  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 11, 0, 6 ) AND obsolete = 0 ORDER BY date_order DESC
DB-233  0.0007  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 11, 0, 6 ) AND obsolete = 0 ORDER BY date_order DESC
DB-234  0.0009  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 11, 0, 6 ) AND obsolete = 0 ORDER BY date_order DESC
DB-235  0.0009  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 196, 0, 192 ) AND obsolete = 0 ORDER BY date_order DESC
DB-236  0.0008  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 196, 0, 192 ) AND obsolete = 0 ORDER BY date_order DESC
DB-237  0.0009  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 196, 0, 192 ) AND obsolete = 0 ORDER BY date_order DESC
DB-238  0.0009  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 59, 0, 21, 9 ) AND obsolete = 0 ORDER BY date_order DESC
DB-239  0.0009  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 59, 0, 21, 9 ) AND obsolete = 0 ORDER BY date_order DESC
DB-240  0.0008  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 59, 0, 21, 9 ) AND obsolete = 0 ORDER BY date_order DESC
DB-241  0.0007  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 117, 0, 6 ) AND obsolete = 0 ORDER BY date_order DESC
DB-242  0.0008  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 117, 0, 6 ) AND obsolete = 0 ORDER BY date_order DESC
DB-243  0.0009  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 117, 0, 6 ) AND obsolete = 0 ORDER BY date_order DESC
DB-244  0.0008  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 135, 0, 82 ) AND obsolete = 0 ORDER BY date_order DESC
DB-245  0.0008  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 135, 0, 82 ) AND obsolete = 0 ORDER BY date_order DESC
DB-246  0.0009  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 135, 0, 82 ) AND obsolete = 0 ORDER BY date_order DESC
DB-247  0.0009  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 103, 0, 7 ) AND obsolete = 0 ORDER BY date_order DESC
DB-248  0.0008  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 103, 0, 7 ) AND obsolete = 0 ORDER BY date_order DESC
DB-249  0.0009  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 103, 0, 7 ) AND obsolete = 0 ORDER BY date_order DESC
DB-250  0.0009  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 121, 0, 97, 7 ) AND obsolete = 0 ORDER BY date_order DESC
DB-251  0.0008  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 121, 0, 97, 7 ) AND obsolete = 0 ORDER BY date_order DESC
DB-252  0.0008  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 121, 0, 97, 7 ) AND obsolete = 0 ORDER BY date_order DESC
DB-253  0.0007  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 73, 0 ) AND obsolete = 0 ORDER BY date_order DESC
DB-254  0.0009  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 73, 0 ) AND obsolete = 0 ORDER BY date_order DESC
DB-255  0.0007  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 73, 0 ) AND obsolete = 0 ORDER BY date_order DESC
DB-256  0.0008  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 60, 0, 21, 9 ) AND obsolete = 0 ORDER BY date_order DESC
DB-257  0.0008  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 60, 0, 21, 9 ) AND obsolete = 0 ORDER BY date_order DESC
DB-258  0.0008  version_api.php:525 version_get_all_rows()  SELECT * FROM mantis_project_version_table WHERE project_id IN ( 60, 0, 21, 9 ) AND obsolete = 0 ORDER BY date_order DESC
TagsNo tags attached.

Activities

There are no notes attached to this issue.

Related Changesets

MantisBT: master 46c77ace

2019-02-11 19:35:55

cproensa


Committer: vboctor Details Diff
Rewrite project versions cache

Rewrite `version_get_all_rows` so it doesn't perform db queries and rely
in `version_cache_array_rows`.

Rewrite `version_cache_array_rows`to clean up code and improve caching
for non existant projects (eg, ALL_PROJECTS), or projects with no
versions.

Both changes fixes
- A recurrent cache miss when ALL_PROJECT was part of the inheritance
projects scope in a query for versions for a project.
- A potentially wrong cached response for project versions (see 0024821).

Fixes: 0023245, 0024821
Affected Issues
0023245, 0024821, 0025661
mod - core/bug_group_action_api.php Diff File
mod - core/version_api.php Diff File

Issue History

Date Modified Username Field Change
2017-08-18 13:03 cproensa New Issue
2019-02-12 18:03 cproensa Assigned To => cproensa
2019-02-12 18:03 cproensa Status new => assigned
2019-02-14 11:01 cproensa Summary project versions are not cached effectively => project versions are not cached efficiently
2019-02-14 11:01 cproensa Additional Information Updated View Revisions
2019-02-24 16:41 vboctor Changeset attached => MantisBT master 46c77ace
2019-02-24 16:41 cproensa Status assigned => resolved
2019-02-24 16:41 cproensa Resolution open => fixed
2019-02-24 16:41 cproensa Fixed in Version => 2.20.0
2019-02-24 16:44 vboctor Target Version => 2.20.0
2019-03-16 20:20 vboctor Status resolved => closed