From f6e0f9fd58ce2f41af8e8c772b92928ec2065c14 Mon Sep 17 00:00:00 2001 From: eileen Date: Thu, 29 Aug 2013 17:29:33 +1200 Subject: [PATCH] changes to group contact cache behaviour that worked on AUG to fix performance bottleneck Conflicts: CRM/Contact/BAO/GroupContactCache.php --- CRM/Contact/BAO/GroupContactCache.php | 21 ++++++++++++++++----- 1 file changed, 16 insertions(+), 5 deletions(-) diff --git a/CRM/Contact/BAO/GroupContactCache.php b/CRM/Contact/BAO/GroupContactCache.php index 90b2cdd62f..14a4c7c70d 100644 --- a/CRM/Contact/BAO/GroupContactCache.php +++ b/CRM/Contact/BAO/GroupContactCache.php @@ -209,14 +209,19 @@ AND g.refresh_date IS NULL // sort the values so we put group IDs in front and hence optimize // mysql storage (or so we think) CRM-9493 sort($values); - + $tempTable = 'civicrm_temp_group_contact_cache' . rand(0,2000); // to avoid long strings, lets do BULK_INSERT_COUNT values at a time while (!empty($values)) { $processed = TRUE; $input = array_splice($values, 0, CRM_Core_DAO::BULK_INSERT_COUNT); $str = implode(',', $input); - $sql = "INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) VALUES $str;"; + $sql = "CREATE TEMPORARY TABLE $tempTable $str;"; CRM_Core_DAO::executeQuery($sql); + CRM_Core_DAO::executeQuery( + "INSERT IGNORE INTO civicrm_group_contact_cache (contact_id, group_id) + SELECT DISTINCT id, group_id FROM $tempTable + "); + CRM_Core_DAO::executeQuery(" DROP TABLE $tempTable"); } self::updateCacheTime($groupID, $processed); } @@ -480,16 +485,22 @@ WHERE civicrm_group_contact.status = 'Added' $groupIDs = array($groupID); self::remove($groupIDs); - $processed = FALSE; + $tempTable = 'civicrm_temp_group_contact_cache' . rand(0,2000); foreach (array($sql, $sqlB) as $selectSql) { if (!$selectSql) { continue; } - $insertSql = "INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) ($selectSql);"; - $processed = TRUE; + $insertSql = "CREATE TEMPORARY TABLE $tempTable ($selectSql);"; + $processed = TRUE; // FIXME $result = CRM_Core_DAO::executeQuery($insertSql); + CRM_Core_DAO::executeQuery( + "INSERT IGNORE INTO civicrm_group_contact_cache (contact_id, group_id) + SELECT DISTINCT id, group_id FROM $tempTable + "); + CRM_Core_DAO::executeQuery(" DROP TABLE $tempTable"); } + self::updateCacheTime($groupIDs, $processed); if ($group->children) { -- 2.25.1