[WIP] [DBAL-218] Add bulk insert query by deeky666 · Pull Request #682 · doctrine/dbal

deeky666 · 2014-09-30T23:40:51Z

This is an approach to make use of database vendors' bulk row insert query syntax.
As the nature of bulk inserts usually is to insert a LOT of rows into a table (primarily from an external source), the implementation tries to focus on good performance and low memory consumption. It is NOT intended for INSERT INTO ... SELECT ... statement queries.

TODO:

Add an "executor" class that encapsulates the BulkInsertQuery object, adds options like bulk size and internally automatically evaluates the underlying platform's limits for a single INSERT statement and splits queries accordingly.
Evaluate platforms' max insert rows per INSERT statement and implement in platforms.
Add unit tests for quoted identifiers.
Add functional tests.

Future additions:

Add support for expressions.
Query builder (if there will be more bulk insert queries like INSERT INTO ... SELECT ...)?

Open for discussion. Ideas welcome!

doctrinebot · 2014-09-30T23:41:08Z

Hello,

thank you for creating this pull request. I have automatically opened an issue
on our Jira Bug Tracker for you. See the issue link:

http://www.doctrine-project.org/jira/browse/DBAL-994

We use Jira to track the state of pull requests and the versions they got
included in.

acrobat · 2014-10-03T08:40:53Z

👍 very good idea!

stof · 2014-10-04T12:46:25Z

lib/Doctrine/DBAL/Platforms/AbstractPlatform.php

what is the meaning of 0 for a max ?

If there is no maximum, I think returning null might be more logical than 0. And 1 might be a better default in the abstract platform rather than unlimited

hmm, no. Given that it throws an exception rather than splitting it into mutliple executions, unlimited is indeed a better default

@stof the value specified in the platform should represent the platform semantics rather than what would be a reasonable default for mass insertions. In general platforms do not have a fixed limit for a single INSERT query, but SQL Server for example has. So unlimited is indeed correct here IMO. The BulkInsertExecutor I am still working on, will then split into multiple INSERT queries when added values reach the platforms maximum value.

:hint: PHP supports INF :hint:

@Ocramius not well. INF is equal to 0 on some OSes. See symfony/symfony#8246 for instance

Does this count?

http://phpsadness.com/sad/52

-----Original message-----
From: Marco Pivetta notifications@github.com
To: doctrine/dbal dbal@noreply.github.com
Cc: Eric Wastl topaz.github@lt3.us
Sent: 2014 Dec, Tue, 16 21:33:56 GMT+00:00
Subject: Re: [dbal] [WIP] [DBAL-218] Add bulk insert query (#682)

@@ -595,6 +595,16 @@ public function getBinaryDefaultLength()
}

/**

\* Returns the maximum number of rows that can be inserted in a
single INSERT statement.

*

\* @return integer

*/

public function getInsertMaxRows()

{

return 0;

@topaz I'm talking about INF == 0 :) We're not that bad in doctrine (yet)

Reply to this email directly or view it on GitHub:
https://github.com/doctrine/dbal/pull/682/files#r21933752

@topaz thanks, that is sad enough :-)

@topaz the issue is actually that on Solaris, we have INF === 0.0

I think it is better to make this method abstract to avoid forgotten implementations in sub-classes and useless default values.

@edefimov not possible until a major version, as adding a new abstract method is a BC break.

billschaller · 2015-03-17T23:19:09Z

NEAT! I was wondering if something like this existed somewhere.

aistis- · 2016-07-05T09:08:33Z

Hi guys, any news?

jonbev · 2016-08-10T09:44:43Z

What's going on with this PR? Would be nice to get this merged....

widmogrod · 2016-08-16T09:26:24Z

Thats a nice feature. Is there anything that I can help to finish it?

edefimov · 2017-06-29T17:58:16Z

lib/Doctrine/DBAL/Query/BulkInsertQuery.php

+    {
+        $valueSet = array();
+
+        if (empty($this->columns)) {


I suggest to refactor method to increase readability

Ocramius · 2017-07-02T10:16:03Z

Moved to a later milestone: while 2.7 may not actually happen (3.0 is a better pick), this can't land in 2.6 yet, even though it is just an improvement.

mbrodala · 2017-11-23T13:59:42Z

What is the API for retrieving the list of all inserted record IDs?

Ocramius · 2017-11-23T15:51:09Z

What is the API for retrieving the list of all inserted record IDs?

Won't be implemented.

mbrodala · 2017-11-27T08:50:03Z

I guess as a workaround one could retrieve the maximum ID before and after the bulk insert which would suffice to get the list of newly added IDs.

Ocramius · 2017-11-27T08:52:36Z

That's NOT a sufficient guarantee when not pre-allocating identifiers. Please do not do that. On 27 Nov 2017 09:50, "Mathias Brodala" <notifications@github.com> wrote: I guess as a workaround one could retrieve the maximum ID before and after the bulk insert which would suffice to get the list of newly added IDs. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#682 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAJakFTFU4bUMbLVJhS38ARSxQdO57yqks5s6ne7gaJpZM4CpR6w> .

mbrodala · 2017-11-27T09:05:26Z

@Ocramius OK, what else do you suggest in that case?

Ocramius · 2017-11-27T09:20:29Z

Simply don't get identifiers for inserts. We will not use bulk inserts for auto-generated fields. Use UUIDs, which are the standard go-to solution for pre-generated identifiers. Marco Pivetta http://twitter.com/Ocramius http://ocramius.github.com/

…

On Mon, Nov 27, 2017 at 10:05 AM, Mathias Brodala ***@***.***> wrote: @Ocramius <https://github.com/ocramius> OK, what else do you suggest in that case? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#682 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAJakAyggh-1SQj-SEql6ilFjxKvlbPWks5s6ntXgaJpZM4CpR6w> .

kiler129 · 2018-02-23T23:02:52Z

@Ocramius What prevents us from continuing with that? After reading all comments I see all problems were resolved.

Majkl578 · 2018-02-24T02:28:30Z

@kiler129 This is most likely to be superseded by #2762.

wujku · 2019-02-13T12:31:28Z

👍

morozov · 2021-10-26T19:23:11Z

Closing as stale.

matheusgontijo · 2022-09-22T21:57:51Z

@morozov Was the bulk insert feature eventually implemented on Doctrine by any chance? Just for my information. Thank you so much.

Koopzington · 2022-11-10T21:57:12Z

Doesn't look like it seeing how the "superseding" PR got closed as stale as well.

dbal/src/Query/QueryBuilder.php

Line 1245 in 0f2d257

' VALUES(' . implode(', ', $this->values) . ')';

speller · 2023-09-04T12:53:43Z

So sad that this feature was not implemented after 9 years from the report....

Ocramius · 2023-09-04T12:58:01Z

@speller send a new PR?

@deeky666 , @morozov and myself are not really active on the project anymore, and OSS comes from YOU first.

Take the feature, champion for it, bring it to the finish line.

add bulk insert query

2cb2cf4

stof reviewed Oct 4, 2014
View reviewed changes

deeky666 added this to the 2.6 milestone Oct 23, 2014

Ocramius removed this from the 2.6 milestone Dec 28, 2014

deeky666 removed this from the 2.6 milestone Dec 28, 2014

doctrinebot mentioned this pull request Dec 7, 2015

DBAL-994: [GH-682] [WIP] [DBAL-218] Add bulk insert query #2242

Closed

darthf1 mentioned this pull request Mar 15, 2016

DBAL-218: Add Object for BulkInsert Abstraction #1392

Closed

Ocramius mentioned this pull request Dec 8, 2016

Caching not improving execution time at all #2563

Closed

edefimov reviewed Jun 29, 2017

View reviewed changes

Ocramius added this to the 2.6 milestone Jun 29, 2017

Ocramius added Improvement Performance QueryBuilder labels Jun 29, 2017

edefimov mentioned this pull request Jun 29, 2017

Bulk inserts #2762

Closed

Ocramius modified the milestones: 2.6, 2.7 Jul 2, 2017

Ocramius mentioned this pull request Jul 7, 2017

Add maximum number of prepared statements to Platform #2748

Closed

prolic mentioned this pull request Dec 14, 2017

Mariadb eventstore returning "null" aggregate on new mariadb server 10.2.11 prooph/pdo-event-store#116

Closed

Ocramius mentioned this pull request Jan 16, 2018

Multiple rows insertions in one query #2981

Closed

Majkl578 modified the milestones: 2.7.0, 2.8.0 Mar 29, 2018

Majkl578 modified the milestones: 2.8.0, 2.9.0 Jun 14, 2018

Ocramius mentioned this pull request Oct 2, 2018

Cascade remove to generate bulk delete SQL doctrine/orm#7418

Open

morozov removed this from the 2.9.0 milestone Dec 2, 2018

Base automatically changed from master to 4.0.x January 22, 2021 07:43

morozov closed this Oct 26, 2021

github-actions bot locked as resolved and limited conversation to collaborators Sep 4, 2024

Uh oh!

Conversation

deeky666 commented Sep 30, 2014

Uh oh!

doctrinebot commented Sep 30, 2014

Uh oh!

acrobat commented Oct 3, 2014

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

billschaller commented Mar 17, 2015

Uh oh!

aistis- commented Jul 5, 2016

Uh oh!

jonbev commented Aug 10, 2016

Uh oh!

widmogrod commented Aug 16, 2016

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Ocramius commented Jul 2, 2017

Uh oh!

mbrodala commented Nov 23, 2017

Uh oh!

Ocramius commented Nov 23, 2017

Uh oh!

mbrodala commented Nov 27, 2017

Uh oh!

Ocramius commented Nov 27, 2017 via email

Uh oh!

mbrodala commented Nov 27, 2017

Uh oh!

Ocramius commented Nov 27, 2017 via email

Uh oh!

kiler129 commented Feb 23, 2018

Uh oh!

Majkl578 commented Feb 24, 2018

Uh oh!

wujku commented Feb 13, 2019

Uh oh!

morozov commented Oct 26, 2021

Uh oh!

matheusgontijo commented Sep 22, 2022

Uh oh!

Koopzington commented Nov 10, 2022 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

speller commented Sep 4, 2023

Uh oh!

Ocramius commented Sep 4, 2023

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

20 participants

Koopzington commented Nov 10, 2022 •

edited

Loading