Uploaded image for project: 'CiviCRM'
  1. CiviCRM
  2. CRM-12715

Merge duplicates is excruciatingly slow on contacts with multiple custom field entries due to cross join

    Details

    • Type: Bug
    • Status: Done/Fixed
    • Priority: Trivial
    • Resolution: Fixed/Completed
    • Affects Version/s: 4.2.9
    • Fix Version/s: 4.4.0
    • Component/s: None
    • Labels:
      None

      Description

      When rendering the dedupe page the join in

      CRM_Core_BAO_CustomGroup::getTree

      joins all relevant custom tables on all others - meaning that an contact with 30 multiple custom value fields across 6 tables returns over 3000 rows (& php parses them all)

      Query joins look like

      LEFT JOIN civicrm_value_campaign_fields_65 USING (entity_id)
      LEFT JOIN civicrm_value_major_donor_information_67 USING (entity_id)
      LEFT JOIN civicrm_value_event_quiz_night_68 USING (entity_id)
      LEFT JOIN civicrm_value_demographic_extra_16 USING (entity_id)
      WHERE civicrm_value_polygons_69.entity_id = 33763

      Code that generates looks like

      if ($firstTable) {
      $fromSQL = $firstTable;
      foreach ($from as $table) {
      if ($table != $firstTable)

      { $fromSQL .= "\nLEFT JOIN $table USING (entity_id)"; }

      }

      $query = "
      SELECT $select
      FROM $fromSQL
      WHERE {$firstTable}.entity_id = $entityID
      ";

      $dao = CRM_Core_DAO::executeQuery($query);

      This is called from lots of places but we only observed slowness here - not sure why. Has anyone hit it?

        Attachments

          Activity

            People

            • Assignee:
              lobo Donald A. Lobo
              Reporter:
              eileen Eileen McNaughton
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: