Does php bindParam() speed up bulk insert?

Question

I've got a .txt file containing 4,000 lines,I'm trying to insert them into mysql,here are two approaches which do the same thing,the first approach is simple coded like this:

$start = microtime(true);
foreach($b as $k=>$v){//$b is an array of 4,000 elements
    $db->exec("INSERT INTO siji (en,cn) VALUES ('$v[0]','$v[1]')");
}
echo microtime(true)-$start;//116 sec.

It takes 116 sec.The second way is using PDO::bindParam(),I know that for repeated SQL query it is a good practice to use bindparam() because the only difference between each query is their values,so I coded like this:

    $start = microtime(true);
$stmt = $db->prepare('INSERT INTO siji (en,cn) VALUES (:en,:cn)');
$stmt->bindParam(':en',$en);
$stmt->bindParam(':cn',$cn);
foreach($b as $k=>$v){//$b is an array of 4,000 elements
    $en = $v[0];
    $cn = $v[1];
    $stmt->execute();//
}
echo microtime(true)-$start;//127 sec.

The second approach is surposed to be faster than the first one,the result is not as what I thought to be though,Could anyone tell me does bindparam() really speed up bulk insertion?Or what could possibly be wrong when using bindparam()?

Answer 1

You haven't specified what database server you're using, so I'll assume MySQL, as it's the most common.

To directly answer your question: The answer is Yes, PDO's prepare function is supposed to use the DB's Prepared Statements functionality, which should result in much faster results when running a batch of similar queries like this.

However specifically with the MySQL PDO driver, it defaults to emulating prepared statements rather than actually using them properly.

This means that by default, inside of the PDO object, it's basically doing exactly the same thing as your first code example, building up the SQL string manually.

I don't know why this is the default behaviour (maybe there was a compatibility issue with older mySQL versions?), but to prevent it and to force PDO to use Prepared Statements properly, you need to disable this option.

You can do this as follows:

$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);

Try that, and see what happens.

By the way, if your .txt file with 4000 lines happens to be a CSV or other regularly formatted file, you could use MySQL's built-in LOAD DATA INFILE function, which can load an entire file into the DB via a single query. This is always much faster than anything you could achieve by looping the same query 4000 times in PHP. (Other DBs have similar functionality).