Details
-
Type: Improvement
-
Status: Open
-
Priority: Minor
-
Resolution: Unresolved
-
Affects Version/s: 4.7.3
-
Fix Version/s: None
-
Component/s: None
-
Labels:
-
Versioning Impact:Patch (backwards-compatible bug fixes)
-
Documentation Required?:None
-
Epic Link:
-
Funding Source:Contributed Code
Description
As seen here:
https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html
There is a quick fix for improving the speed of bulk insert statements (when using mysql/innodb).
SET autocommit=0;
... SQL import statements ...
COMMIT;
In our tests, wrapping this around the insert statements for loading contacts into groups reduces the time it takes to rebuild a ~300'000 contact smart group by about half.
Before change::
Time taken is: 10.650987148285
After change::
Time taken is:: 5.0638861656189
Time taken is: 4.3147821426392
Time taken is: 4.2649991512299
CRM_Contact_BAO_GroupContactCache.php ~Line 53
CRM_Core_DAO::executeQuery("SET autocommit=0"); CRM_Core_DAO::executeQuery( "INSERT IGNORE INTO civicrm_group_contact_cache (contact_id, group_id) SELECT DISTINCT $idName, group_id FROM $tempTable"); CRM_Core_DAO::executeQuery("COMMIT");
This approach could be used for other areas large inserts are performed?