Prepared statements with multiple inserts compared to a regular query

Question

If you consider the following table

table_A (id (PK), value1, value2)

If I want to insert a data set, for example: (1,5), (1,3), (3,5)

I could fulfill the request, for example:

INSERT INTO table_A (value1, value2) VALUES (1,5), (1,3), (3,5)

which will work. However, they tell me that prepared statements will be better. Looking at the prepared statements, I think I needed to do something like this.

 $stmt = $dbh->prepare("INSERT INTO table_A (value1, value2) VALUES (?, ?)"); $stmt->bindParam(1, $value1); $stmt->bindParam(2, $value2); //for each set of values $value1 = 1; $value2 = 5; $stmt->execute(); 

My question is, how can a prepared statement be better (in terms of performance) than the first method? One is one request, the other is multiple executions of the same request. Is the first query compiled into three separate queries or something else?

Answer 1

A prepared statement alone will not be faster if you insert it only once. However, if you need to run the same inserts several times, you will save the time required to analyze the request and prepare the request plan. The finished operator insert will be parsed once, the plan for it will be cached, and then reused for all subsequent inserts. On the other hand, a statement with several built-in values ​​will need to be reworked each time a new one is launched, slowing down the process.

Network rounding, on the other hand, is also slow. This can be slower if you are doing an additional circuit than for parsing and preparing a query plan, so you should consult before making a decision one way or another.