View Issue Details

IDProjectCategoryView StatusLast Update
0020139mantisbtperformancepublic2016-06-12 00:43
ReportercproensaAssigned Tocproensa 
PrioritynormalSeverityminorReproducibilityalways
Status closedResolutionfixed 
Product Version1.3.0-beta.3 
Target Version1.3.0-rc.2Fixed in Version1.3.0-rc.2 
Summary0020139: reduce print_subproject_option_list sql query count
Description

in "print_api.php"
function print_subproject_option_list

prints the option list of subprojects one by one, each call may generate a sql query for individual project if project row is not loaded in cache. This is the case of rendering the project selector, which is usually on the first elements in a page.

(note that print_project_option_list does prefetch project list into cache before iterating them)

TagsNo tags attached.

Activities

cproensa

cproensa

2015-09-25 16:51

developer   ~0051534

PR: https://github.com/mantisbt/mantisbt/pull/653

cproensa

cproensa

2015-09-25 16:53

developer  

dblog_cached_rows.txt (2,325 bytes)
DB-20	0.0009	project_api.php:147 project_cache_array_rows()	SELECT * FROM mantis_project_table WHERE id IN (122,6,82,113,130,9,73,8,7)
DB-21	0.0011	user_api.php:1043 user_get_accessible_subprojects()	SELECT DISTINCT p.id, p.name, ph.parent_id FROM mantis_project_table p LEFT JOIN mantis_project_hierarchy_table ph ON ph.child_id = p.id WHERE p.enabled = 1 AND ph.parent_id IS NOT NULL ORDER BY p.name
DB-22	0.0014	project_api.php:147 project_cache_array_rows()	SELECT * FROM mantis_project_table WHERE id IN (129,126,124,123,127,125)
DB-23	0.0018	project_api.php:147 project_cache_array_rows()	SELECT * FROM mantis_project_table WHERE id IN (54,11,62,17,81,117,108,104,109,68,70,79,78,16,116,12,111)
DB-24	0.0015	project_api.php:147 project_cache_array_rows()	SELECT * FROM mantis_project_table WHERE id IN (15,13,14)
DB-25	0.0014	project_api.php:147 project_cache_array_rows()	SELECT * FROM mantis_project_table WHERE id IN (84,83,135,85)
DB-26	0.0017	project_api.php:147 project_cache_array_rows()	SELECT * FROM mantis_project_table WHERE id IN (114,115)
DB-27	0.0101	project_api.php:147 project_cache_array_rows()	SELECT * FROM mantis_project_table WHERE id IN (106,23,22,80,58,75,138,112,67,76,25,77,91,56,92,21,61,107,140)
DB-28	0.0023	project_api.php:147 project_cache_array_rows()	SELECT * FROM mantis_project_table WHERE id IN (137)
DB-29	0.0011	project_api.php:147 project_cache_array_rows()	SELECT * FROM mantis_project_table WHERE id IN (51,59,29,27,26,131)
DB-30	0.0020	project_api.php:147 project_cache_array_rows()	SELECT * FROM mantis_project_table WHERE id IN (66)
DB-31	0.0011	project_api.php:147 project_cache_array_rows()	SELECT * FROM mantis_project_table WHERE id IN (141)
DB-32	0.0012	project_api.php:147 project_cache_array_rows()	SELECT * FROM mantis_project_table WHERE id IN (90)
DB-33	0.0015	project_api.php:147 project_cache_array_rows()	SELECT * FROM mantis_project_table WHERE id IN (88,36,37,38,49,39,40,41,89,45,47)
DB-34	0.0018	project_api.php:147 project_cache_array_rows()	SELECT * FROM mantis_project_table WHERE id IN (69,31,33,119)
DB-35	0.0013	project_api.php:147 project_cache_array_rows()	SELECT * FROM mantis_project_table WHERE id IN (98,96,101,18,72,100,95,99,133,97)
DB-36	0.0011	project_api.php:147 project_cache_array_rows()	SELECT * FROM mantis_project_table WHERE id IN (121)
dblog_cached_rows.txt (2,325 bytes)
cproensa

cproensa

2015-09-25 16:54

developer  

dblog_not_cached.txt (9,138 bytes)
DB-20	0.0007	project_api.php:147 project_cache_array_rows()	SELECT * FROM mantis_project_table WHERE id IN (122,6,82,113,130,9,73,8,7)
DB-21	0.0007	user_api.php:1043 user_get_accessible_subprojects()	SELECT DISTINCT p.id, p.name, ph.parent_id FROM mantis_project_table p LEFT JOIN mantis_project_hierarchy_table ph ON ph.child_id = p.id WHERE p.enabled = 1 AND ph.parent_id IS NOT NULL ORDER BY p.name
DB-22	0.0009	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=129
DB-23	0.0007	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=126
DB-24	0.0006	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=124
DB-25	0.0007	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=123
DB-26	0.0010	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=127
DB-27	0.0006	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=125
DB-28	0.0007	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=54
DB-29	0.0007	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=11
DB-30	0.0007	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=62
DB-31	0.0007	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=17
DB-32	0.0010	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=81
DB-33	0.0009	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=117
DB-34	0.0007	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=108
DB-35	0.0007	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=104
DB-36	0.0013	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=109
DB-37	0.0009	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=68
DB-38	0.0009	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=70
DB-39	0.0006	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=79
DB-40	0.0006	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=78
DB-41	0.0006	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=16
DB-42	0.0006	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=116
DB-43	0.0007	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=12
DB-44	0.0008	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=15
DB-45	0.0006	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=13
DB-46	0.0007	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=14
DB-47	0.0006	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=111
DB-48	0.0006	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=84
DB-49	0.0006	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=83
DB-50	0.0006	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=135
DB-51	0.0009	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=85
DB-52	0.0009	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=114
DB-53	0.0006	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=115
DB-54	0.0007	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=106
DB-55	0.0007	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=23
DB-56	0.0010	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=22
DB-57	0.0009	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=80
DB-58	0.0006	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=58
DB-59	0.0006	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=75
DB-60	0.0005	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=138
DB-61	0.0005	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=137
DB-62	0.0005	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=112
DB-63	0.0005	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=67
DB-64	0.0005	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=76
DB-65	0.0006	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=25
DB-66	0.0005	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=77
DB-67	0.0009	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=91
DB-68	0.0007	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=56
DB-69	0.0005	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=92
DB-70	0.0005	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=21
DB-71	0.0009	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=51
DB-72	0.0006	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=59
DB-73	0.0005	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=29
DB-74	0.0005	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=27
DB-75	0.0006	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=26
DB-76	0.0005	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=131
DB-77	0.0005	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=66
DB-78	0.0005	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=61
DB-79	0.0006	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=107
DB-80	0.0005	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=140
DB-81	0.0005	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=141
DB-82	0.0006	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=90
DB-83	0.0006	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=88
DB-84	0.0005	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=36
DB-85	0.0006	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=37
DB-86	0.0005	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=38
DB-87	0.0006	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=49
DB-88	0.0006	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=69
DB-89	0.0006	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=31
DB-90	0.0006	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=33
DB-91	0.0006	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=119
DB-92	0.0005	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=39
DB-93	0.0017	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=40
DB-94	0.0013	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=41
DB-95	0.0008	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=89
DB-96	0.0010	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=45
DB-97	0.0009	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=47
DB-98	0.0008	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=98
DB-99	0.0007	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=96
DB-100	0.0011	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=101
DB-101	0.0011	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=18
DB-102	0.0012	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=72
DB-103	0.0012	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=100
DB-104	0.0008	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=95
DB-105	0.0008	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=99
DB-106	0.0009	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=133
DB-107	0.0010	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=97
DB-108	0.0010	project_api.php:106 project_cache_row()	SELECT * FROM mantis_project_table WHERE id=121
dblog_not_cached.txt (9,138 bytes)
cproensa

cproensa

2015-09-25 16:55

developer   ~0051536

attached example execution with current code and after fix.

Related Changesets

MantisBT: master 770df68e

2015-09-25 16:37:15

cproensa


Committer: dregad Details Diff
Improve sql performance print_project_option_list

Cache subprojects before processing them to reduce the number of SQL
queries executed.

Previously, only first level projects got cached, resulting in repeated
queries (one for each project).

Fixes 0020139

Signed-off-by: Damien Regad <dregad@mantisbt.org>
mod - core/print_api.php Diff File

Issue History

Date Modified Username Field Change
2015-09-25 16:45 cproensa New Issue
2015-09-25 16:51 cproensa Note Added: 0051534
2015-09-25 16:53 cproensa File Added: dblog_cached_rows.txt
2015-09-25 16:54 cproensa File Added: dblog_not_cached.txt
2015-09-25 16:55 cproensa Note Added: 0051536
2015-12-20 17:55 dregad Changeset attached => MantisBT master 770df68e
2015-12-20 17:55 dregad Assigned To => dregad
2015-12-20 17:55 dregad Status new => resolved
2015-12-20 17:55 dregad Resolution open => fixed
2015-12-20 17:55 dregad Fixed in Version => 1.3.0-rc.2
2015-12-20 17:56 dregad Target Version => 1.3.0-rc.2
2015-12-21 13:50 atrol Assigned To dregad => community
2016-01-19 15:23 atrol Assigned To community => cproensa
2016-06-12 00:43 vboctor Status resolved => closed