Performance bug in Required Products add-on: Slow Product edit page executes approximately 4,000 SQL queries

Bug tracker
,
1

Hello,

I found a significant performance issue in the standard Required Products
add-on on the backend product update page.

Environment

Edition: CS-Cart Ultimate
Version: [Multi-Vendorv4.20.1]
PHP: 8.2
Web server: Nginx
Database: Separate database VPS (intranet) on the same dedicated server.
Average network latency between the application and database servers: 0.315 ms

Affected file

app/addons/required_products/controllers/backend/products.post.php

Steps to reproduce

  1. Enable the standard Required Products add-on.
  2. Open an existing product in the administration panel.
  3. Measure the request with the CS-Cart debugger.
  4. Compare the results after disabling the Required Products add-on.

Original result with Required Products enabled

Page generation time: 7.2822 seconds
SQL query count: 3,973
SQL execution time: 5.6124 seconds

Result with Required Products disabled

Page generation time: 1.004 seconds
SQL query count: 364
SQL execution time: 0.429 seconds

The debugger backtrace showed this call chain:

app/addons/required_products/controllers/backend/products.post.php:34
fn_get_product_data()
fn_get_product_features()
fn_get_product_feature_variants()
db_get_field()

The add-on calls fn_get_product_data($product_id), apparently only to retrieve
the company_id value.

This causes all product features and feature variants to be loaded. In our
case, similar COUNT queries against product_feature_variants were executed
thousands of times.

Current code

$product_data = fn_get_product_data($product_id);
$product_company_id = !empty($product_data)
? $product_data[‘company_id’]
: 0;

Tested fix

$product_company_id = (int) db_get_field(
‘SELECT company_id FROM ?:products WHERE product_id = ?i’,
$product_id
);

Result after the fix, with Required Products enabled

Page generation time: 1.1474 seconds
SQL query count: 423
SQL execution time: 0.47735 seconds

The product edit page became approximately 6.3 times faster.

Functional verification

The Required Products tab still works.
The required-product relation is saved correctly.
The main product and required product are both automatically added to the cart.

Could you please confirm whether this is a bug and consider replacing the full
fn_get_product_data() call with a direct company_id lookup in an official
release?

Best regards,
Emre

2

Hello

If cannot patch the core add-on file (it will be overwritten on upgrade), there is an upgrade-safe workaround: a small custom add-on that handles the get_product_data_pre hook and forces the features flag off on the product edit page.

init.php:

fn_register_hooks(‘get_product_data_pre’);

func.php:

function fn_myaddon_get_product_data_pre(
&$product_id, &$auth, &$lang_code, &$field_list, &$get_add_pairs, &$get_main_pair,
&$get_taxes, &$get_qty_discounts, &$preview, &$features, &$skip_company_condition, &$params
) {
if (
SiteArea::isAdmin(AREA)
&& isset($_REQUEST[‘dispatch’])
&& $_REQUEST[‘dispatch’] === ‘products.update’
) {
$features = false;
}
}
Patching the one line in required_products is still the cleaner fix.

Best regards
Robert