I have a csv file that I'm trying to import with 30,000+ records of a data, spread across 40 or so fields, half of which are taxonomy terms. The initial import takes something like 12 hours to complete even though the mysql cpu resource rarely goes above 10% utilisation.

Subsequent updates are generally fine because only a few records are ever updated and the feeds hash seems to do the job nicely - the problem comes when new fields need to be added or the same field for every record updates.

After looking through the module code, I can't see where I would need to apply a patch to speed up the import - would someone be able to advise? Is this even possible, or should I be looking at the Migrate module instead?

Thanks in advance.

Comments

attiks’s picture

attiks commented

We ran into the same problem, while trying to import 10.000 users with 30+ fields, meaning on average 60+ insert per user. We solved it by writing our own importer that converts all inserts into one stored procedures, bringing down the number of inserts per user to 2: 1 plain user_save to save name + email and to get a uid, 1 call to the stored procedures. I can not remember the speed improvement but it was huge.

Only other solution is to do it in plain sql, we used this approach for the initial import of 500.000 users.

PS: I think migrate (and any other module) will suffer the same problems.

zeezhao’s picture

zeezhao commented

Hi - please any tips for increasing performance if loading csv file with 500,000+ rows? Seems to do about 30,000/hr.

attiks’s picture

attiks commented

30,000/hr sounds pretty fast already so there's now easy way to speed this, things you can try:

  1. write your own importer and use a stored procedure to do all the inserts at once
  2. move your database to RAM while importing
sukh.singh’s picture

sukh.singh commented

Attiks, can you please elaborate how you did this? If possible please share you code as well.

twistor’s picture

twistor commented

You can try setting the $conf['feeds_process_limit'] in settings.php to something much larger than the default, which is 50. What this means is that a new batch process will be spawned after every 50 items. For taxonomy terms, you should be able to get this much larger.

attiks’s picture

attiks commented

#5 the limit isn't the problem, the problem is that there're a lot of inserts, and each insert is send as a separate db_query from the php side.

The following code shows how you can create a stored proc, it uses a mapping ($importer->mappings) to map column names from the csv to the right field.

The created stored proc has 2 extra parameters (p_start AND p_end) to limit the amount of records processed in one go. We use queues to loop over all values.


function custom_importer_create($id = NULL) {
  $importer = custom_importer_get_importer($id);
  if ($importer) {
    $base_name = 'custom_importer_' . $id . '_';
    
    // Drop everything first
    db_query('DROP PROCEDURE IF EXISTS ' . $base_name . 'proc');
    db_query('DROP TABLE IF EXISTS ' . $base_name . 'data');
    db_query('DROP TABLE IF EXISTS ' . $base_name . 'import');
    
    // Get mappings for stored procedure
    $mappings = explode("\n", $importer->mappings);
    $insertfields = array();
    foreach ($mappings as $mapping) {
      $field = explode(':', $mapping);
      if (isset($field[1]) && !empty($field[1])) {
        $insertfields[trim($field[1])] = trim($field[0]);
      }
    }

    // Build stored procedure
    $procedure_sql = 'CREATE PROCEDURE ' . $base_name . 'proc';
    $ins_sql = "";
    $info_instances = field_info_instances($importer->type);
    $info_instances = $info_instances[$importer->type];
    $field_types = field_info_field_types();
    foreach ($insertfields as $f => $i) {
      if (strpos($f, 'field_') === 0) {
        $field = field_info_field($info_instances[$f]['field_name']);
        $type = strtolower($field_types[$field['type']]['label']);
        $ins_columns = "(entity_type, bundle, entity_id, language, delta, " . $f . "_value";
        $ins_values = "'user', 'user', uid, 'und', 0, " . $f;
        $fieldfound = TRUE;
        switch ($type) {
          case 'text':
            $ins_columns .= ", " . $f . "_format";
            $ins_values .= ", 1";
            break;
          case 'float':
          case 'boolean':
          case 'date':
          case 'integer':
            break;
          default:
            $fieldfound = FALSE;
        }
        $ins_columns .= ")";
        if ($fieldfound) {
          $ins_sql .= "INSERT INTO {field_data_" . $f . "} " . $ins_columns;
          $ins_sql .= " SELECT " . $ins_values . " FROM {" . $base_name . 'data' . "} WHERE uid IS NOT NULL ";
          $ins_sql .= " AND id BETWEEN p_start AND p_end ";
          $ins_sql .= " ON DUPLICATE KEY UPDATE " . $f . "_value = " . $f;
          $ins_sql .= "; ";
        }
      }
    }
    
    $procedure_sql .= "(p_start INT, p_end INT) ";
    $procedure_sql .= "BEGIN ";
    $procedure_sql .= $ins_sql;
    $procedure_sql .= "END";
    $res = db_query($procedure_sql);
    // Build data table
    $var_sql = "";
    
    // For users add uid and mail
    $var_sql .= "id INTEGER UNSIGNED NOT NULL DEFAULT NULL AUTO_INCREMENT, ";
    $var_sql .= "uid INT, ";
    $var_sql .= "name VARCHAR(255), ";
    $var_sql .= "mail VARCHAR(255), ";
    $var_sql .= "language VARCHAR(255), ";
    
    foreach ($insertfields as $f => $i) {
      if (strpos($f, 'field_') === 0) {
        $field = field_info_field($info_instances[$f]['field_name']);
        $type = strtolower($field_types[$field['type']]['label']);
        switch ($type) {
          case 'text':
            if (!empty($field_types[$field['type']]['settings']['max_length'])) {
              $var_sql .= $f . " VARCHAR(" . $field_types[$field['type']]['settings']['max_length'] . "), ";
            }
            else {
              $var_sql .= $f . " VARCHAR(255), ";
            }
            break;
          case 'float':
            $var_sql .= $f . " FLOAT, ";
            break;
          case 'boolean':
            $var_sql .= $f . " INT(11), ";
            break;
          case 'date':
          case 'date (iso format)':
            $var_sql .= $f . " VARCHAR(20), ";
            break;
          case 'integer':
            $var_sql .= $f . " INT, ";
            break;
          case '':
            break;
          default:
            drupal_set_message(t('Unknown type found: !type', array('@type' => $type)));
        }
      }
    }
    
    $var_sql = substr($var_sql, 0, -2);
    $table_sql = "CREATE TABLE {" . $base_name . "data}" . "(" . $var_sql . ", PRIMARY KEY (`id`)) ";
    $res = db_query($table_sql);

    // Build import table
    $csvfields = explode("\n", $importer->columns);
    $insertfields = array();
    foreach ($csvfields as $csvfield) {
      $field = explode(':', $csvfield);
      if (isset($field[1]) && !empty($field[1])) {
        $insertfields[trim($field[0])] = trim($field[1]);
      }
    }
    
    $table_sql = 'CREATE TABLE {' . $base_name . 'import}';
    $var_sql = "";
    
    foreach ($insertfields as $f => $t) {
      $var_sql .= "`" . $f . "` " . $t . ", ";
    }
    
    $var_sql = substr($var_sql, 0, -2);
    $table_sql .= "(" . $var_sql . ") ";
    db_query($table_sql);
    
    drupal_set_message(t('Importer tables and procedures created'));
  }
}
internetdevels’s picture

internetdevels commented

if revisions are not necessary u can use http://drupal.org/project/field_sql_norevisions
it will give 20-25% speed improvement

bluegeek9’s picture

bluegeek9 commented
Issue summary: View changes
Status: Active » Closed (outdated)

Drupal 7 reached end of life and the D7 version of Feeds is no longer being developed. To keep the issue queue focused on supported versions, we’re closing older D7 issues.

If you still have questions about using Feeds on Drupal 7, feel free to ask. While we won’t fix D7 bugs anymore, we’re happy to offer guidance to help you move forward. You can do so by opening (or reopening) a D7 issue, or by reaching out in the #feeds channel on Drupal Slack.

If this issue is still relevant for Drupal 10+, please open a follow-up issue or merge request with proposed changes. Contributions are always welcome!

Now that this issue is closed, review the contribution record.

As a contributor, attribute any organization that helped you, or if you volunteered your own time.

Maintainers, credit people who helped resolve this issue.