View Issue Details
ID | Project | Category | View Status | Date Submitted | Last Update |
---|---|---|---|---|---|
0032131 | mantisbt | performance | public | 2023-03-13 11:48 | 2023-03-15 11:47 |
Reporter | polzin | Assigned To | dregad | ||
Priority | normal | Severity | minor | Reproducibility | always |
Status | resolved | Resolution | fixed | ||
Product Version | 2.25.6 | ||||
Target Version | 2.25.7 | Fixed in Version | 2.25.7 | ||
Summary | 0032131: access_project_array_filter can lead to many SQL requests | ||||
Description | In In our setup it reduced the SQL requests per page from 300 to 50 when inserting: | ||||
Tags | No tags attached. | ||||
Thanks for the report, it seems a good optimization and should not cause much side effects. How may projects/subprojects do you have in your system ? Would you be able to submit a pull request with the proposed change ? |
|
@dregad. Not now. Could you do it for me? It´s just one line... :-) |
|
Maybe I'm missing something here, but after a quick check I am not seeing any difference in the number of executed queries or page load time, with or without the project_cache_array_rows() call added in access_project_array_filter() as suggested. Stack trace with unmodified 2.25.6 source -> 12 queries
Stack trace with the added project_cache_array_rows() in access_project_array_filter() -> 12 queries
Note that project_cache_array_rows() is already called for all user-accessible projects in layout_navbar_projects_list(), per the following stack trace (which actually occurs slightly later than with your proposed change.
My test DB has 103 projects. I tested from manage_overview_page.php, but results are the same from heavier pages as well. Some more information about your setup (number of projects, visibility, subprojects...) would be useful to understand in what situation the performance improvement occurs. |
|
@dregad It happend on view.php pages. I have no reports for other pages. If it is really relevant for you, I delete the fix from our installations, and ask people to try other pages. |
|
Then maybe it's caused by a combination of limited access levels and/or visibility on private projects ?
As mentioned before, Also check for these users, which access level they have, and which project(s) they are member of (how many of the total). Maybe verify if you have any plugins that could affect this as well.
Probably safe yes, but I like to understand the reason and consequence of changing something ;-)
I don't think that's needed. I can test with view.php too |
|
We have 322 projects in a project hierachy, all internal, so users get individual access rights to all enabled objects. I can´t count how many of them are enabled, but most are. The users had "developer" level (the affected users and the others, too). |
|
Are the projects/subprojects public or private?
Is this their global access level ? |
|
All projects/subproject are private. |
|
OK I got it :-) It turns out the problem is not driven by the project hierarchy as I thought initially, but by the user's access level. I did not catch it in my initial repro tests (0032131:0067483) because I was logged in with an admin user. The logic that determines whether the Project selector is shown depends on the user having access to more than one project, or having access to manage functions. This is handled by layout_navbar_can_show_projects_menu(), which calls access_has_any_project_level() to see if user has manage_project_threshold level. So the access_has_any_project_level() call occurs earlier for non-admin users, as shown in this stack trace (with DEVELOPER user, master branch 7cf0e76b5):
Compare with ADMIN stack trace:
Bottomline is, your fix is good... And now I understand why ;-) |
|
I still don´t get, why for other non-admin users there was no problem. But I am fine with the fix. :-) Thanks!! |
|
Do these users have access to any of the manage pages (e.g. projects, users, custom fields - see layout_manage_menu_link() function) ? |
|
MantisBT: master-2.25 8ccd29c5 2023-03-15 08:05 Details Diff |
Cache Projects in access_project_array_filter() This performance optimization avoids execution of one SQL query per Project in the loop as we check the access level for each one. Fixes 0032131 |
Affected Issues 0032131 |
|
mod - core/access_api.php | Diff File |