6

Is it possible to insert several entities to DB with a single query? When I use an example from here I can see several queries in the Web Debugger

UPDATED 23.08.2012

I found the following related links. I hope it will help to someone to understand a batch processing:

  1. http://www.doctrine-project.org/blog/doctrine2-batch-processing.html
  2. doctrine2 - How to improve flush efficiency?
  3. Doctrine 2: weird behavior while batch processing inserts of entities that reference other entities

The main things:

Some people seem to be wondering why Doctrine does not use multi-inserts (insert into (...) values (...), (...), (...), ...

First of all, this syntax is only supported on mysql and newer postgresql versions. Secondly, there is no easy way to get hold of all the generated identifiers in such a multi-insert when using AUTO_INCREMENT or SERIAL and an ORM needs the identifiers for identity management of the objects. Lastly, insert performance is rarely the bottleneck of an ORM. Normal inserts are more than fast enough for most situations and if you really want to do fast bulk inserts, then a multi-insert is not the best way anyway, i.e. Postgres COPY or Mysql LOAD DATA INFILE are several orders of magnitude faster.

These are the reasons why it is not worth the effort to implement an abstraction that performs multi-inserts on mysql and postgresql in an ORM. I hope that clears up some questionmarks.

Community
  • 1
  • 1
Mikhail
  • 2,542
  • 4
  • 29
  • 40

1 Answers1

0

I think that there will be several insert statements, but only one query to the database per "flush" call.

As mentioned here http://doctrine-orm.readthedocs.org/en/2.0.x/reference/working-with-objects.html

Each "persist" will add an operation to the current UnitOfWork, then it is the call to EntityManager#flush() which will actually write to the database (encapsulating all the operation of the UnitOfWork in a single transaction).

But I have not checked that the behavior I describe above is the actual behavior.

Best regards, Christophe

user1041440
  • 191
  • 1
  • 7
  • Thanks for your response, but I'm not sure that `only one query to the database per "flush" call`, because I can see a time of execution for each INSERT statement. It means that each INSERT statement is one query to DB. I updated my question – Mikhail Aug 23 '12 at 07:10