Speed - dataset overview pages take a LONG time to load and filter

[lucyWMDE]

I count over 20 seconds to load some filters (eg filter for "Bild", "text", any of the Thema filters) if they're not already cached. Is there anything we can do to speed it up? Project overview pages seem to load much faster.

[Snater]

Thing is the query is very complex. The plain query (without any filters) looks like this:

SELECT DISTINCT subQuery.nid AS nid
FROM
{node} node
INNER JOIN (SELECT node_field_data.created AS node_field_data_created, node_field_data.nid AS nid, paragraphs_item_field_data_node__field_events.id AS paragraphs_item_field_data_node__field_events_id, users_field_data_node_field_data.uid AS users_field_data_node_field_data_uid, taxonomy_term_field_data_node__field_file_types.tid AS taxonomy_term_field_data_node__field_file_types_tid, taxonomy_term_field_data_node__field_keywords.tid AS taxonomy_term_field_data_node__field_keywords_tid, taxonomy_term_field_data_node__field_metadata_format.tid AS taxonomy_term_field_data_node__field_metadata_format_tid, taxonomy_term_field_data_node__field_metadata_standards.tid AS taxonomy_term_field_data_node__field_metadata_standards_tid, taxonomy_term_field_data_node__field_theme.tid AS taxonomy_term_field_data_node__field_theme_tid, paragraphs_item_field_data_node__field_media_license.id AS paragraphs_item_field_data_node__field_media_license_id, taxonomy_term_field_data_paragraph__field_license.tid AS taxonomy_term_field_data_paragraph__field_license_tid
FROM
{node_field_data} node_field_data
LEFT JOIN {node__field_events} node__field_events ON node_field_data.nid = node__field_events.entity_id AND node__field_events.deleted = '0'
LEFT JOIN {paragraphs_item_field_data} paragraphs_item_field_data_node__field_events ON node__field_events.field_events_target_revision_id = paragraphs_item_field_data_node__field_events.revision_id
LEFT JOIN {users_field_data} users_field_data_node_field_data ON node_field_data.uid = users_field_data_node_field_data.uid
LEFT JOIN {node__field_file_types} node__field_file_types ON node_field_data.nid = node__field_file_types.entity_id AND node__field_file_types.deleted = '0'
LEFT JOIN {taxonomy_term_field_data} taxonomy_term_field_data_node__field_file_types ON node__field_file_types.field_file_types_target_id = taxonomy_term_field_data_node__field_file_types.tid
LEFT JOIN {node__field_keywords} node__field_keywords ON node_field_data.nid = node__field_keywords.entity_id AND (node__field_keywords.deleted = '0' AND node__field_keywords.langcode = node_field_data.langcode)
LEFT JOIN {taxonomy_term_field_data} taxonomy_term_field_data_node__field_keywords ON node__field_keywords.field_keywords_target_id = taxonomy_term_field_data_node__field_keywords.tid
LEFT JOIN {node__field_metadata_format} node__field_metadata_format ON node_field_data.nid = node__field_metadata_format.entity_id AND node__field_metadata_format.deleted = '0'
LEFT JOIN {taxonomy_term_field_data} taxonomy_term_field_data_node__field_metadata_format ON node__field_metadata_format.field_metadata_format_target_id = taxonomy_term_field_data_node__field_metadata_format.tid
LEFT JOIN {node__field_metadata_standards} node__field_metadata_standards ON node_field_data.nid = node__field_metadata_standards.entity_id AND node__field_metadata_standards.deleted = '0'
LEFT JOIN {taxonomy_term_field_data} taxonomy_term_field_data_node__field_metadata_standards ON node__field_metadata_standards.field_metadata_standards_target_id = taxonomy_term_field_data_node__field_metadata_standards.tid
LEFT JOIN {node__field_theme} node__field_theme ON node_field_data.nid = node__field_theme.entity_id AND node__field_theme.deleted = '0'
LEFT JOIN {taxonomy_term_field_data} taxonomy_term_field_data_node__field_theme ON node__field_theme.field_theme_target_id = taxonomy_term_field_data_node__field_theme.tid
LEFT JOIN {node__field_media_license} node__field_media_license ON node_field_data.nid = node__field_media_license.entity_id AND node__field_media_license.deleted = '0'
LEFT JOIN {paragraphs_item_field_data} paragraphs_item_field_data_node__field_media_license ON node__field_media_license.field_media_license_target_revision_id = paragraphs_item_field_data_node__field_media_license.revision_id
LEFT JOIN {paragraph__field_license} paragraphs_item_field_data_node__field_media_license__paragraph__field_license ON paragraphs_item_field_data_node__field_media_license.id = paragraphs_item_field_data_node__field_media_license__paragraph__field_license.entity_id AND paragraphs_item_field_data_node__field_media_license__paragraph__field_license.deleted = '0' AND (paragraphs_item_field_data_node__field_media_license__paragraph__field_license.langcode = paragraphs_item_field_data_node__field_media_license.langcode OR paragraphs_item_field_data_node__field_media_license__paragraph__field_license.bundle IN ( 'attributed_image', 'project_image' ))
LEFT JOIN {taxonomy_term_field_data} taxonomy_term_field_data_paragraph__field_license ON paragraphs_item_field_data_node__field_media_license__paragraph__field_license.field_license_target_id = taxonomy_term_field_data_paragraph__field_license.tid
WHERE (node_field_data.type IN ('data_set')) AND (node_field_data.status = '1')
ORDER BY node_field_data_created ASC) subQuery
LIMIT 24 OFFSET 0

There is a lot of referencing by entities and some references are also looped through paragraphs. This setup helps keeping the data consistent, but as we experience, this is not performant in terms of querying. And the problem is that the query load obviously increases just considerably the more datasets are to be queried.

I don't know how much it would be possible (and I am not sure if it would actually make sense) to aggregate more server performance. From the development perspective, there are different solutions, yet none is particularly nice. Especially at this stage of development.

• We could potentially reduce complexity by cutting down entity references with the downside of having to enter information twice which, then, might lead to inconsistencies and errors.
• We could of course remove some filters.
• We could create a separate database/table for building up a search index so we would not need to query the complex relations at runtime, but just query that one table instead. I think this would be a desirable solution, but that is not a thing I could invest time into right now.

There's probably other solutions as well, but that's what I can think of right now.

[mbuechner]

Can't say anything to that, except SQL query is way too long, solution three probably the best one. ;-)

[ikyriazi]

Dear @flokosiol, have you started working on this issue?

[flokosiol]

@alex-knaub and I had a closer look on this issue and he wanted to contact you – he probably already did.

[ikyriazi]

Dear @alex-knaub and @flokosiol, we are still waiting for the 3st-module regarding the improvement of loading time for our data (and project) page. A delivery before Christmas would be highly desirable. Thank you in advance!

[ikyriazi]

As of last week the issue has been resolved. Thank you all!