Possible RFC: PDOStatement::addBatch

3 years ago by Vinicius Dias — view source — reply

unread

Hello, folks. I hope you all are well.

I was talking to friend about a feature I would like to see in PHP but
I mentioned that unfortunately I do not have the knowledge to try
implementing it myself, since it is not something very simple, so he
mentioned that I could bring this discussion up to this list and see
if there would be a person willing to develop that.

The feature is a new method in PDOStatement called addBatch to allow
batch INSERTs, exactly like Java's JDBC1 has. This method would
allow us having one single INSERT SQL query, adding multiple values to
it, and sending this to the database at once, as one single query.

The idea would be something like this:


&lt;?php

$pdo = // PDO connection
$dataRows = ['first value', 'second value', 'third value'];
$statement = $pdo->prepare('INSERT INTO table (column) VALUES (?);');

foreach ($dataRows as $row) {
    $statement->bindValue(1, $row, \PDO::PARAM_STR);
    $statement->addBatch();
}

$statement->execute(); // or ->executeBatch if that's easier

Currently, to achieve the same goal, we need to perform string
interpolation (or concatenation) to assemble the batch INSERT query
and bind every value at once.
I have worked on a lot of projects where developers would perform
multiple different INSERT queries and if this method existed, it would
be a simple change to optimize those pieces of code.

As I mentioned, I don't have the knowledge to create the PR myself,
but I would be more than happy to help as I can. That includes writing
the RFC if it gets to that point (fingers crossed).

I am looking forward to hearing back your comments on this.
Thank you very much,
Vinicius Dias.

3 years ago by Kamil Tekiela — view source — reply

unread

Hi Vinicius,

What would be the reason to add this? A reason cannot just be that there is
no such function. You can easily do it with a loop like you have just
shown. There isn't really any other way unless we introduce some driver
specific SQL builder. So what would we gain from it? Is it speed, security,
efficiency?

String concatenation isn't an alternative though.

Regards,
Kamil

3 years ago by Benjamin Morel — view source — reply

unread

Hello, folks. I hope you all are well.

I was talking to friend about a feature I would like to see in PHP but
I mentioned that unfortunately I do not have the knowledge to try
implementing it myself, since it is not something very simple, so he
mentioned that I could bring this discussion up to this list and see
if there would be a person willing to develop that.

The feature is a new method in PDOStatement called addBatch to allow
batch INSERTs, exactly like Java's JDBC[1] has. This method would
allow us having one single INSERT SQL query, adding multiple values to
it, and sending this to the database at once, as one single query.

Hi Vinicius,

For what it's worth, this is somehow possible in userland by using
concatenated queries and PDOStatement::nextRowset():

$statement = $pdo->prepare('
SELECT ?;
SELECT ?, ?;
SELECT ?, ?, ?;
');

$statement->execute([
    'one',
    'two', 'three',
    'four', 'five', 'six'
]);

do {
    print_r($statement->fetchAll(PDO::FETCH_ASSOC));
} while ($statement->nextRowset());

Please note that this will only work with PDO::ATTR_EMULATE_PREPARES set to
true (default value), so this is of (very) limited use, as it's most likely
still sent to the db as individual queries.
Plus it comes with potential caveats regarding concatenating queries, as
implied by Kamil.

If batching was native to PDO, and brought performance improvements (making
less round trips to the database), I'd be all for it.

In the meantime, if you just want to speed up consecutive INSERT
statements, you might want to use extended inserts instead:

INSERT INTO table (a, b) VALUES (1, 2), (3, 4), (5, 6);

I have a library that abstracts this for you, if that helps:
https://github.com/brick/db

Benjamin

3 years ago by Vinicius Dias — view source — reply

unread

Hi Vinicius,

What would be the reason to add this? A reason cannot just be that there is no such function. You can easily do it with a loop like you have just shown. There isn't really any other way > unless we introduce some driver specific SQL builder. So what would we gain from it? Is it speed, security, efficiency?

String concatenation isn't an alternative though.

Regards,
Kamil

Great question, Kamil. This new method would not add new capabilities to PHP.
It would make things easier for a batch insert. Currently, we would
need something like the following. This is a real world production
code, not written by me. I just decreased the number of columns and
changed table/column names:

&lt;?php

$values = implode(",", array_fill(0, count($data_list), "(?, ?)"));
$pdo = PDODataDB::getInstance();
$statement = $pdo->prepare(
"INSERT INTO `table_name`(
`column_1`,
`column_2`
) VALUES
{$values}"
);
$counter = 0;
foreach ($data_list as $data) {
    $statement->bindValue(++$counter, $data['specific_key'], PDO::PARAM_INT);
    $statement->bindValue(++$counter, $data['another_key'], PDO::PARAM_INT);
}
$statement->execute();
$statement = null;

So when I said about string concatenation, that's what I meant. With
the new method, we would have something like:

&lt;?php

$pdo = PDODataDB::getInstance();
$statement = $pdo->prepare("INSERT INTO `table_name`(`column_1`,
`column_2`) VALUES (?, ?)");
foreach ($data_list as $data) {
    $statement->bindValue(1, $data['specific_key'], PDO::PARAM_INT);
    $statement->bindValue(2, $data['another_key'], PDO::PARAM_INT);
    $statemnt->addBatch();
}
$statement->execute();
$statement = null;

But the biggest pro isn't this case. It's the case where beginners
would have multiple individual INSERT queries being separately sent to
the database because they are not familiar with the approach of
concatenating the parameters to the query string. I hope I made it a
bit more clear now, so to sum it up: this new method would not bring
any new functionality to PHP, but it would:

Make the code a bit more clear;
Help newcomers to write bulk INSERTs.

I have a library that abstracts this for you, if that helps: https://github.com/brick/db

Benjamin

That BulkInserter class would basically be the point of this new
addition to the core, Benjamin. But a lot of new developers are not
even familiar with composer, so adding an external library is out of
their reality. Having an addBatch method in PHP's core would
definitely help them write a better and more performatic code.

I once again appreciate the attention and the replies.
Vinicius Dias.

3 years ago by mickmackusa — view source — reply

unread

So if I'm not missing something, you want to add this new method to core to
accommodate situations where:

the dev is too inexperienced to implement a pre-existing library and
the incoming 2d payload is not packaged as a csv file (and therefore
cannot be directly imported as such) and
the application does not benefit from accessing the auto-incremented ids
generated by each insert and
the performance of the bulk insertions resolves a crippling bottleneck
in performance?

IMO, the total footprint of your implemented snippet isn't significantly
"sleeker/sexier" than using concatenation. I don't see anything ugly or
unreadable about building two flat arrays, then using them to construct a
SQL string and feed the execute() method. https://phpize.online/s/iW

I am not an RFC voter, but I don't really see a compelling reason to add
this method to the language.

mickmackusa