No announcement yet.

Data import of Contacts slow and getting slower

  • Filter
  • Time
  • Show
Clear All
new posts

  • Data import of Contacts slow and getting slower

    Hi, I am in the process of importing a lot of Contacts into the system (50,000+). I have already loaded up the Accounts which went well, but the Contact data is slow and getting slower (about 1 row per second as I write with 12,000 rows loaded and lots more to come)

    So I dug out the MySQL tools and went to work to identify what was going on.

    I set up the trap for slow running queries and one specific query came to the top of the list. It alone takes about 0.6 seconds of the 0.7 second per row.

    I then spent some time with the EXPLAIN tool and worked out that a complete table scan of the Contact table is being performed every time the query runs.

    The full query is below but this is the problematic segment:
    WHERE ((contact.first_name = 'Joe' AND contact.last_name = 'Bloggs') OR ( = ''))
    AND contact.deleted = '0'
    The actual problem is caused by the two items in the OR clause. If I take either clause out, or change the "OR" to an "AND" then the index is used and the query is almost instant. As is it avoids the table scan.

    I don't know why the query optimiser is doing this or what can be done about it but imports are very slow.

    Help would be appreciated.

    In my specific situation I know that the records are new so I am happy to skip the check to see if they are there already.

    For reference here is the full query:
    SELECT DISTINCT AS `id` , TRIM(CONCAT(IFNULL(contact.first_name,''),' ', IFNULL(contact.last_name,''))) AS `name`, 
    contact.deleted AS `deleted`, contact.salutation_name AS `salutationName`, contact.first_name AS `firstName`, 
    contact.last_name AS `lastName`, contact.account_id AS `accountId`, accountContact.role AS `title`, contact.description AS `description`, AS `emailAddress`, AS `phoneNumber`, contact.do_not_call AS `doNotCall`, contact.address_street AS `addressStreet`, 
    contact.address_city AS `addressCity`, contact.address_state AS `addressState`, contact.address_country AS `addressCountry`, 
    contact.address_postal_code AS `addressPostalCode`, account.type AS `accountType`, contact.created_at AS `createdAt`, 
    contact.modified_at AS `modifiedAt`, contact.core_id AS `coreId`, contact.core_id_num AS `coreIdNum`, AS `accountName`, contact.campaign_id AS `campaignId`, contact.created_by_id AS `createdById`, 
    TRIM(CONCAT(createdBy.first_name,' ', createdBy.last_name)) AS `createdByName`, 
    contact.modified_by_id AS `modifiedById`, TRIM(CONCAT(modifiedBy.first_name,' ', modifiedBy.last_name)) AS `modifiedByName`, contact.assigned_user_id AS `assignedUserId`, 
    TRIM(CONCAT(assignedUser.first_name,' ', assignedUser.last_name)) AS `assignedUserName` 
    FROM `contact`
    LEFT JOIN `account` AS `account` ON contact.account_id = 
    LEFT JOIN `user` AS `assignedUser` ON contact.assigned_user_id =
    LEFT JOIN `user` AS `modifiedBy` ON contact.modified_by_id =
    LEFT JOIN `user` AS `createdBy` ON contact.created_by_id =
    LEFT JOIN `entity_email_address` AS `emailAddressesMiddle` ON = emailAddressesMiddle.entity_id  AND emailAddressesMiddle.deleted = '0' AND emailAddressesMiddle.primary = '1' AND emailAddressesMiddle.entity_type = 'Contact'
    LEFT JOIN `email_address` AS `emailAddresses` ON = emailAddressesMiddle.email_address_id AND emailAddresses.deleted ='0'
    LEFT JOIN `entity_phone_number` AS `phoneNumbersMiddle` ON = phoneNumbersMiddle.entity_id AND phoneNumbersMiddle.deleted = '0' AND phoneNumbersMiddle.primary = '1' AND phoneNumbersMiddle.entity_type = 'Contact'
    LEFT JOIN `phone_number` AS `phoneNumbers` ON = phoneNumbersMiddle.phone_number_id AND phoneNumbers.deleted = '0'
    LEFT JOIN `entity_email_address` AS `emailAddressesMultipleMiddle` ON = emailAddressesMultipleMiddle.entity_id AND emailAddressesMultipleMiddle.deleted = '0' AND emailAddressesMultipleMiddle.entity_type = 'Contact'
    LEFT JOIN `email_address` AS `emailAddressesMultiple` ON = emailAddressesMultipleMiddle.email_address_id AND emailAddressesMultiple.deleted = '0'
    LEFT JOIN `account_contact` AS accountContact ON accountContact.contact_id = AND accountContact.account_id = contact.account_id AND accountContact.deleted = 0
    WHERE ((contact.first_name = 'John' AND contact.last_name = 'Doe') OR ( = '')) AND contact.deleted = '0'

  • #2
    Now is available 4.3 beta version of EspoCRM, which allows execute import in idle (for big data; via cron) and skip searching for duplicates


    • #3
      Thanks for the response, sounds great.

      I am new to the forum so can you point me to where I can get the beta and any notes around this feature please?



      • #4
        EspoCRM 4.3.0-beta.2: ( Upgrade from 4.2.7 to


        • #5
          Thanks for the continued help.

          I now have a problem applying the upgrade, not sure where to post it so will put it here. Please advise if the wrong place.

          I am applying the upgrade to a Ubuntu server that I have full access to. My EspoCRM is installed in /var/www/html/espo and I am using Apache.

          Following the guidance I am installing it from the command line so after becoming the Apache user (www-data on my server) here is the transcript:

          www-data@dev4:~/html/espo$ php upgrade.php /home/shares/Temp/E*3*zip
          Current version is 4.2.7
          Start upgrade process...
          Error: Permission denied for <br>client/res/templates/fields/email/search.tpl, <br>client/res/templates/fields/phone/search.tpl
          The two files referenced do not exist but the directories do with other files (detail.tpl, edit.tpl, list.tpl). The only place where they are referenced is in the "manifest.json" file that comes with the upgrade.

          Help as always appreciated.



          • #6
            Having done some more investigations I think my version has been upgraded as I have access to some of the new features (in particular the one I want).

            When I go to the "About" item in the menu it still reports 4.2.7 though.

            So I have either upgraded and the beta has not updated the version number or I have a partial upgrade. I am not sure which.



            • #7
              Please try to upgrade your instance one more time. It should work fine.
              Job Offers and Requests


              • #8
                Ok, in digging around it seems that I had the beta 1 zip file. This would always generate an error, no matter how many times I ran it.

                I have since found the beta 2 zip and while it failed the 1st time, it did complete the 2nd. Thanks for the help.

                I don't know if this matters but the "About" screen still claims to be 4.2.7 but the features do seem to be there.

                Thanks for the help.


                • #9
                  If you can change version in data/config.php to 4.3.0-beta.2