$stmt = $db->prepare( "INSERT INTO Allocation (ParameterID, Rate) " .
"VALUES (:ParameterID, :Rate);" );
foreach ($screen as $key => $value) {
$stmt->execute( array( ":ParameterID" => $key,
":Rate" => $value ) );
}
It was simple and straight-forward, but horribly inefficient, taking about 20 seconds to upload the 70 key/value pairs. Worse, the Rackspace Cloud Sites hosting that we use automatically times out web pages after 30 seconds, meaning that this was coming dangerously close to simply dumping the data onto the floor. This month, I had more time to put some focused thought into it, and optimize the code as follows; it quietly gathers ten pairs at a time before uploading, and now (predictably) runs ten times as quickly:
function saveSome( $db, $some ) {
try {
if (count( $some ) == 0)
return TRUE;
$sql = "INSERT INTO Allocation (ParameterID, Rate) VALUES ";
$np = array();
$i = 0;
foreach ($some as $key => $value) {
if ($i > 0)
$sql .= ", ";
$sql .= "(:ParameterID$i, :Rate$i)";
$np[ ":ParameterID$i" ] = $key;
$np[ ":Rate$i" ] = $value;
$i++;
}
$sql .= ";";
$stmt = $db->prepare( $sql );
$stmt->execute( $np );
return TRUE;
} catch (PDOException $e) {
fail( $e->getMessage() );
}
return FALSE;
}
function saveAll( $db, $screen ) {
try {
$some = array();
foreach ($screen as $key => $value) {
$some[ $key ] = $value;
if (count( $some ) >= 10) {
// Save a subset and start a new one:
if (saveSome( $db, $some ) === FALSE)
return FALSE;
$some = array();
}
}
// Save final subset (if any):
return saveSome( $db, $some );
} catch (PDOException $e) {
fail( $e->getMessage() );
}
return FALSE;
}
