search

minvws / nl-kat-coordination

Repo nl-kat-coordination for minvws
European Union Public License 1.2
121 stars 54 forks source link

Improve KATalogus indexing pages #2524

Open ammar92 opened 4 months ago

ammar92 commented 4 months ago

Is your feature request related to a problem? Please describe. Currently when listing a KATalogus page from Rocky (such as listing type of plugins or a viewing a specific plugin), the page load time exceeds 300 ms. This is mainly due to a lot of individual SQL queries performed within the KATalogus backend. This could be caused by the way we use dependency injection within FastAPI or other simple factors.

Describe the solution you'd like I'd like to see less queries performed, especially in the detail view. And in the list view we should use a broader SQL query rather than per plugin based queries. In any case they should probably not be the same query. But first we should investigate the root cause by analyzing the code.

Additional context

image

These seems to be repeated SQL calls with the following query:

SELECT plugin_state.id AS plugin_state_id, plugin_state.plugin_id AS plugin_state_plugin_id, plugin_state.enabled AS plugin_state_enabled, plugin_state.organisation_pk AS plugin_state_organisation_pk, plugin_state.repository_pk AS plugin_state_repository_pk 
FROM plugin_state JOIN organisation ON organisation.pk = plugin_state.organisation_pk JOIN repository ON repository.pk = plugin_state.repository_pk 
WHERE plugin_state.organisation_pk = organisation.pk AND plugin_state.repository_pk = repository.pk AND plugin_state.plugin_id = %(plugin_id_1)s AND organisation.id = %(id_1)s AND repository.id = %(id_2)s 
 LIMIT %(param_1)s
underdarknl commented 4 months ago

https://github.com/minvws/nl-kat-coordination/blob/2a6e2e3b59ca030cc2fcf89cac3d240f56f990db/boefjes/boefjes/katalogus/dependencies/plugins.py#L69C9-L69C113

ammar92 commented 4 months ago

https://github.com/minvws/nl-kat-coordination/blob/2a6e2e3b59ca030cc2fcf89cac3d240f56f990db/boefjes/boefjes/katalogus/dependencies/plugins.py#L69C9-L69C113

This indeed leads to repeatedly calling https://github.com/minvws/nl-kat-coordination/blob/2a6e2e3b59ca030cc2fcf89cac3d240f56f990db/boefjes/boefjes/katalogus/dependencies/plugins.py#L190

We should probably also look into using PluginEnabledStorage.get_all_enabled instead somewhere