At the department of Astronomy, university of Geneva, we developed a Drupal application for scientific data products:
https://www.astro.unige.ch/mmoda/gallery
The data are stored in nodes (some content types created) and views are created to display and search different kinds of data. Starting from some amount of nodes (> 60’000 nodes) the performance degraded drastically when views are rendered.
Example :
Very slow page : https://www.astro.unige.ch/mmoda/gallery/astrophysical-entity/gx-14
Acceptable time : 1ES 0033+595 | MMODA Product Gallery
Many queries are generated by the views (hundreds !). A large number of them are affecting the same table and returning one row !.
It’s clear that having the data split among multiple tables (fields tables) does not help at all : multiple SQL joins !
We want to know if some of you had such experience and advise us if they succeed to improve the performance. One way would be probably by rewritting the part of the views related to queries using Drupal hooks.
Without a meta-model few queries should be enough and the amount of data is not that big to affect the performance.
Querying a table of few thousands of rows is still acceptable but the fact the Drupal views engine is generating tons of queries is the cause of this performance degradation.
We think also about moving to use proprietary data model (own tables) …
Drupal version : 9.
Any help on this is welcome…
Kind regards,