How can I prepare a database statement and execute it many times with different input?

Question

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();

Answer 1

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);