Avoid extra DB query per menu item

Created on 3 December 2021, about 3 years ago
Updated 3 July 2023, over 1 year ago

Problem/Motivation

The translatable_menu_link_uri_iterate_menu() function loads MenuLinkContent entities by their UUID, which actually triggers a database query for every content link displayed on a page, even if the entity was already loaded by ID.

Steps to reproduce

  • Install Translatable menu link uri module.
  • Install Web Profiler sub-module of the Devel project.
  • Create a menu and add multiple content links there.
  • Put a block with this menu to the page.
  • Visit the page with the menu block visible.
  • Check the database log for queries like the following:
SELECT "base_table"."revision_id" AS "revision_id", "base_table"."id" AS "id" 
FROM "menu_link_content" "base_table" 
INNER JOIN "menu_link_content" "menu_link_content" ON "menu_link_content"."id" = "base_table"."id" 
INNER JOIN "menu_link_content_data" "menu_link_content_data" ON "menu_link_content_data"."id" = "base_table"."id" 
WHERE ("menu_link_content"."uuid" IN (:db_condition_placeholder_0)) 
AND ("menu_link_content_data"."default_langcode" IN (:db_condition_placeholder_1))

Proposed resolution

Not yet, see remaining tasks.

Remaining tasks

It's up to module maintainer to choose the approach here. Possible options are:

  • The \Drupal\menu_link_content\Plugin\Menu\MenuLinkContent::getEntity() does a very nice job of loading all the items by ID at once and it's better to re-use the method. However, this method is not open to public. Please check ✨ Set MenuLinkContent getEntity to public visibility Fixed .
  • Alternative approach would be to explore the MenuLinkContent definition, find entity ID saved there at $this-pluginDefinition['metadata']['entity_id'] and try to load the entity by ID. This way entity storage is able to take it from the static cache and it eliminates the extra query too. It could also collect entity IDs from all the menu items and then load them at once by IDs. However, this adds dependency on some internals of the Menu Link Content module and backward compatibility doesn't seem to be guaranteed for such things.

User interface changes

No.

API changes

No.

Data model changes

No.

πŸ› Bug report
Status

Needs work

Version

2.0

Component

Code

Created by

πŸ‡΅πŸ‡ΉPortugal dmitriy.trt

Live updates comments and jobs are added and updated live.
Sign in to follow issues

Comments & Activities

Not all content is available!

It's likely this issue predates Contrib.social: some issue and comment data are missing.

  • πŸ‡ΊπŸ‡ΈUnited States angrytoast PNW

    The core issue ✨ Set MenuLinkContent getEntity to public visibility Fixed to expose \Drupal\menu_link_content\Plugin\Menu\MenuLinkContent::getEntity() looks like it will finally happen which makes Option 1 the best approach.

    This is to update the patch to work with the latest 2.x branch code. It also adds a more specific check for MenuLinkContentInterface instead of just EntityInterface because we are looking for a menu link content entity.

  • πŸ‡ΊπŸ‡ΈUnited States angrytoast PNW

    After experimenting a bit, it looks like we can further simplify code with a public MenuLinkContent::getEntity(). The entity retrieved via the method is already the correct translation, so we can skip the entity.repository call.

    Adding an updated patch with the simplified logic.

  • Status changed to Needs review over 1 year ago
  • πŸ‡ΊπŸ‡ΈUnited States angrytoast PNW
  • πŸ‡©πŸ‡ͺGermany Anybody Porta Westfalica

    Thank you, that's looking really good! I'm a bit afraid of things breaking, so would be nice to have automated test coverage. Then we can RTBC and merge this with confidence!

    And perhaps use a MR instead?

  • πŸ‡·πŸ‡΄Romania bboty

    I've tested #4 https://www.drupal.org/files/issues/2023-07-06/translatable_menu_link_ur... β†’

    and there is an issue. It's calling a protected method "getEntity()" that causes WSOD.

    $url = $link->getEntity()->link_override->first();
    
  • πŸ‡ͺπŸ‡ΈSpain eduardo morales alberti Spain, πŸ‡ͺπŸ‡Ί

    Eduardo Morales Alberti β†’ made their first commit to this issue’s fork.

  • Status changed to Fixed 3 months ago
  • πŸ‡΅πŸ‡±Poland jsobiecki Wroclaw

    I really like this change! The code looks much simpler and the fact that we reduced SQL queries number of site is very important.

    I share concern of @Anybody, that we may fall into some of regressions and unit tests can help reduce that risk. But I'd create a initial unit (rather kernel) test as separate tasks. This module was designed as quick and simple workaround in D8 times and I wanted to keep it that way. As we have D11 and it's still helpful, it's good idea to improve its quality. But not now, I'll do that as separate issue.

    I did some small refactor of code, as patch failed to apply and did manual testing. I was no able to "break" module after this modification.

  • Automatically closed - issue fixed for 2 weeks with no activity.

  • Status changed to Fixed 27 days ago
  • πŸ‡§πŸ‡ͺBelgium seutje Antwerp

    This change broke compatibility with D9 as the method being used is still protected (and there aren't any plans on changing that in D9).

    Might be worth flagging it as such so automated composer updates running on D9 sites don't update to 2.1.1, throwing fatal errors all around...

  • πŸ‡΅πŸ‡±Poland jsobiecki Wroclaw

    OH! Thanks for reporting. I'll deploy fix today later

  • πŸ‡΅πŸ‡±Poland jsobiecki Wroclaw

    I pushed fix for the issue on D9. I also added very basic (just loading of service) Kernel test, hopefully it will be start for test suite that will solve problems like this. It's late for me today, but tomorrow I'll try do some additional tests and I'll push the next release that works on both D9 and D10.

Production build 0.71.5 2024