Improve product loop performance by statically caching field group lookup queries

Impact: 85 · Confidence: 100 · Complexity: 25

Description

Problem: On product archive pages (shop/category loops), PPOM performs a large number of duplicate database queries. Every product in the loop triggers NM_PersonalizedProduct::product_supports(), executing identical calls to FieldGroupRepository::find_rows_with_categories_or_tags() and get_rows_by_productmeta_ids() for each product, resulting in hundreds or thousands of duplicate queries per page load.

Desired Behavior: The database lookups in FieldGroupRepository should leverage a static (in-request) cache. A query with a specific set of arguments should only hit the database once. Subsequent calls with the same arguments during the same request should return the statically cached result, optimizing loop processing natively without needing to bypass PPOM logic via a new setting.

Acceptance Criteria:

• Implement static in-memory caching for FieldGroupRepository::find_rows_with_categories_or_tags().
• Implement static caching for FieldGroupRepository::get_rows_by_productmeta_ids().
• Verify via Query Monitor that loading a shop archive page with multiple PPOM-enabled products yields 0 duplicate queries for these specific repository methods.

Customer Context

A Pro-tier user with roughly 52 field groups is experiencing severe performance degradation on loop pages (homepage, shop). Via Query Monitor, they identified 368 duplicate calls to find_rows_with_categories_or_tags() and 214 calls to get_rows_by_productmeta_ids() per page load. They requested an option to either add static caching or introduce a UI toggle to disable PPOM in loops.

Root Cause Analysis

The root cause of the performance bottleneck is that PPOM does not statically cache field group dataset retrievals during a single page load. When the NM_PersonalizedProduct::product_supports() check is executed in a WooCommerce product loop, the identical database queries run iteratively for every single product variation/item rendered on the screen. The customer's proposed solution (a setting to skip PPOM on loops) is a workaround for this lack of query optimization.

Alternative Suggestions

Instead of building a new user-facing setting to skip PPOM processing in product loops, implement request-level (static) caching inside the FieldGroupRepository methods (find_rows_with_categories_or_tags and get_rows_by_productmeta_ids). This resolves the performance issue inherently for all users by eliminating duplicate DB calls and keeps the settings panel clean.

Reasoning

The customer requested a new setting to disable PPOM processing in product loops (archives/shop) to mitigate severe performance issues caused by duplicate database queries. However, creating a setting just to avoid poor query performance masks the underlying architectural flaw. The better solution is to improve the existing repository methods by implementing a static in-memory cache per request so that duplicate queries are eliminated globally without requiring user configuration.

Source: HelpScout #3357274189
Generated by feature-request-triage workflow (ID: feature-request-triage_6a31650c78be78.78526881)

Start by locating FieldGroupRepository and reading find_rows_with_categories_or_tags() and get_rows_by_productmeta_ids(), then reproduce the duplicate queries on a WooCommerce shop or archive page with Query Monitor. Done means repeated calls with identical arguments use request-level cached results and Query Monitor shows no duplicate queries for these methods.