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?