2

I have a situation where I need to update a Drupal module's query to run a SELECT query [and based on the results] an INSERT for a data set of ~a million items.

Outside of Drupal I would accomplish this by first preparing my SELECT and INSERT statements, then executing them once for each item:

$db = new PDO(...);
$check = $db->prepare('SELECT COUNT(*) FROM other_tablename WHERE colname = :value');
$insert = $db->prepare('INSERT INTO tablename (colname) VALUES (:value)');
foreach ($my_many_items as $item) {
  $check->execute(array(':value' => $item));
  if ($check->fetchColumn() < 1) {
    $insert->execute(array(':value' => $item));
  }
}

With the Drupal 7 DB-API however, there seems to be no way to get a prepared statement without executing it first using db_query($query, array(':value' => $item), array('return' => Database::RETURN_STATEMENT)).

The only option I've found is to run db_query() / db_select() / db_insert() in a loop for each item which ends up requiring the query string to be re-parsed for every single iteration.

How can I prepare a database statement (with Drupal doing its table-name rewriting) and then execute it many times with different input?

Background:

The existing implementation uses db_select() to build a query using a giant SELECT ... IN clause, filters the items in PHP, then uses db_insert() to save all of the values at once. While this implementation only runs two queries, it unfortunately blows up the memory usage when handling large numbers of items:

...
$result = db_select('other_tablename', 'o')
  ->fields('o', array('colname'))
  ->condition('colname', array($my_many_items),'IN')
  ->execute()
  ->fetchAll();
$existing = array();
foreach ($result as $r) {
  $existing[] = $r->colname;
}
$insert = db_insert('tablename')->fields(array('colname'));
foreach ($my_many_items as $item) {
  if (!in_array($item, $existing)) {
    $insert->values(array('colname' => $value));
  }
}
$insert->execute();
4

Prepared statements can be created after changing the statement class to PDOStatement. This technique is described in How to execute stored procedures in drupal?

// Get the Drupal database connection and change the statement class to PDOStatement.
// Save the current class for cleanup later.
$conn = Database::getConnection();
$saved_class = $conn->getAttribute(PDO::ATTR_STATEMENT_CLASS);
$conn->setAttribute(PDO::ATTR_STATEMENT_CLASS, array('PDOStatement'));

// code from the question:
$check = $conn->prepare('SELECT COUNT(*) FROM other_tablename WHERE colname = :value');
$insert = $conn->prepare('INSERT INTO tablename (colname) VALUES (:value)');
foreach ($my_many_items as $item) {
  $check->execute(array(':value' => $item));
  if ($check->fetchColumn() < 1) {
    $insert->execute(array(':value' => $item));
  }
}

// Revert the connection to its previous statement class.
$conn->setAttribute(PDO::ATTR_STATEMENT_CLASS, $saved_class);
| improve this answer | |
  • Thanks for the answer, the $conn = Database::getConnection(); $saved_class = $conn->getAttribute(PDO::ATTR_STATEMENT_CLASS); $conn->setAttribute(PDO::ATTR_STATEMENT_CLASS, array('PDOStatement')); part in the How to execute stored procedures in drupal? answer was what I needed, the first example in your answer was what I'm trying to avoid. If you remove that first example and reword to I'll mark this as the accepted answer. I'm happy to edit the answer if you'd prefer. – Adam Franco Dec 8 '15 at 18:18
  • @AdamFranco Edit the answer, and I accep the edit, and was a pleasure help you, ;-) – Adrian Cid Almaguer Dec 8 '15 at 18:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.