Why bindParam is faster than bindValue?

I’m referring to this page of the official guide: http://www.yiiframework.com/doc-2.0/guide-db-dao.html#binding-parameters

It shows these examples:




// first example 

$command = $db->createCommand('SELECT * FROM post WHERE id=:id');


$post1 = $command->bindValue(':id', 1)->queryOne();

$post2 = $command->bindValue(':id', 2)->queryOne();


// second example

$command = $db->createCommand('SELECT * FROM post WHERE id=:id')

              ->bindParam(':id', $id);


$id = 1;

$post1 = $command->queryOne();


$id = 2;

$post2 = $command->queryOne();



It states that "Notice that you bind the placeholder to the $id variable before the execution, and then change the value of that variable before each subsequent execution (this is often done with loops). Executing queries in this manner can be vastly more efficient than running a new query for every different parameter value "

My question is: why? Technically, I mean.

I both cases, Yii 2 is preparing a SQL statement, than is executing a param ‘replacing’, than is executing this the single query. 2 Query, 2 params binded as total.

Why this promised performance difference?

Before Database runs a SQL statement it checks it’s valid and determines how to access the tables and join them together. This is called parsing. The optimizer has the task of figuring out which table access and join methods to use. This produces an execution plan. When Database sends a statement to the optimizer to do this it’s called a hard parse.

If a plan already exists for a query, Database doesn’t need to go through the optimization process again. It can reuse the existing plan. This is referred to as soft parsing and is performant.