Performance issues with Store Credit Module in admin area
0Store Credit is great module, unfortunately it’s preety inefecient in some places. For example, on every page request of admin/store_credit.php whole customers table is read, and for each customer additional SELECT and UPDATE query is executed to calculate and update pending points, even if the customer does not have any pending points!
It’s not a big deal when You have up to 1000 customers, but when there are more then 100 000 customers page loads in 30 seconds (store_credit.php executes over 2 x customer count, or in this example over 200 000 queries on each request).
To address that, change following code in admin/store_credit.php:
global $db;
$customers = $db->Execute("SELECT customers_id FROM " . TABLE_CUSTOMERS . " ORDER BY customers_id ASC");
while (!$customers->EOF) {
$customers_id = $customers->fields[‘customers_id’];
$pending_rewards = $this->get_pending_rewards($customers_id);
$db->Execute("UPDATE " . TABLE_STORE_CREDIT . "
SET pending = " . $pending_rewards . "
WHERE customers_id = " . $customers_id . "
LIMIT 1");
$customers->MoveNext();
}
}
to:
global $db;
$customers = $db->Execute("SELECT DISTINCT customers_id FROM " . TABLE_SC_REWARD_POINT_LOGS . " ORDER BY customers_id ASC");
while (!$customers->EOF) {
$customers_id = $customers->fields[‘customers_id’];
$pending_rewards = $this->get_pending_rewards($customers_id);
if($pending_rewards == 0.0) { $customers->MoveNext(); continue; }
$db->Execute("UPDATE " . TABLE_STORE_CREDIT . "
SET pending = " . $pending_rewards . "
WHERE customers_id = " . $customers_id . "
LIMIT 1");
$customers->MoveNext();
}
}
Thanks to that, only customers who actually have some pending points will be processed.
