Newsgroups: php.internals
Path: news.php.net
Xref: news.php.net php.internals:120574
MIME-Version: 1.0
References: <CAEwRQKYbasnxH1ePjOVfJA1ruSHoG_hHjUoZdvzy3AbQS36KXQ@mail.gmail.com>
In-Reply-To: <CAEwRQKYbasnxH1ePjOVfJA1ruSHoG_hHjUoZdvzy3AbQS36KXQ@mail.gmail.com>
Date: Wed, 14 Jun 2023 12:43:36 +0200
Message-ID: <CAG9XoMTPZ_kQzFvd3cnBqKh6Qjg5y6ve-SefN1=Z+s9-wFcNpg@mail.gmail.com>
To: Vinicius Dias <carlosv775@gmail.com>
Cc: internals@lists.php.net
Content-Type: multipart/alternative; boundary="0000000000006164a205fe14a084"
Subject: Re: [PHP-DEV] Possible RFC: PDOStatement::addBatch
From: benjamin.morel@gmail.com (Benjamin Morel)

--0000000000006164a205fe14a084
Content-Type: text/plain; charset="UTF-8"

On Wed, 14 Jun 2023 at 01:41, Vinicius Dias <carlosv775@gmail.com> wrote:

> 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

--0000000000006164a205fe14a084--