batch-processing.rst 5.2 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179
  1. Batch Processing
  2. ================
  3. This chapter shows you how to accomplish bulk inserts, updates and
  4. deletes with Doctrine in an efficient way. The main problem with
  5. bulk operations is usually not to run out of memory and this is
  6. especially what the strategies presented here provide help with.
  7. .. warning::
  8. An ORM tool is not primarily well-suited for mass
  9. inserts, updates or deletions. Every RDBMS has its own, most
  10. effective way of dealing with such operations and if the options
  11. outlined below are not sufficient for your purposes we recommend
  12. you use the tools for your particular RDBMS for these bulk
  13. operations.
  14. Bulk Inserts
  15. ------------
  16. Bulk inserts in Doctrine are best performed in batches, taking
  17. advantage of the transactional write-behind behavior of an
  18. ``EntityManager``. The following code shows an example for
  19. inserting 10000 objects with a batch size of 20. You may need to
  20. experiment with the batch size to find the size that works best for
  21. you. Larger batch sizes mean more prepared statement reuse
  22. internally but also mean more work during ``flush``.
  23. .. code-block:: php
  24. <?php
  25. $batchSize = 20;
  26. for ($i = 1; $i <= 10000; ++$i) {
  27. $user = new CmsUser;
  28. $user->setStatus('user');
  29. $user->setUsername('user' . $i);
  30. $user->setName('Mr.Smith-' . $i);
  31. $em->persist($user);
  32. if (($i % $batchSize) === 0) {
  33. $em->flush();
  34. $em->clear(); // Detaches all objects from Doctrine!
  35. }
  36. }
  37. Bulk Updates
  38. ------------
  39. There are 2 possibilities for bulk updates with Doctrine.
  40. DQL UPDATE
  41. ~~~~~~~~~~
  42. The by far most efficient way for bulk updates is to use a DQL
  43. UPDATE query. Example:
  44. .. code-block:: php
  45. <?php
  46. $q = $em->createQuery('update MyProject\Model\Manager m set m.salary = m.salary * 0.9');
  47. $numUpdated = $q->execute();
  48. Iterating results
  49. ~~~~~~~~~~~~~~~~~
  50. An alternative solution for bulk updates is to use the
  51. ``Query#iterate()`` facility to iterate over the query results step
  52. by step instead of loading the whole result into memory at once.
  53. The following example shows how to do this, combining the iteration
  54. with the batching strategy that was already used for bulk inserts:
  55. .. code-block:: php
  56. <?php
  57. $batchSize = 20;
  58. $i = 0;
  59. $q = $em->createQuery('select u from MyProject\Model\User u');
  60. $iterableResult = $q->iterate();
  61. foreach($iterableResult AS $row) {
  62. $user = $row[0];
  63. $user->increaseCredit();
  64. $user->calculateNewBonuses();
  65. if (($i % $batchSize) === 0) {
  66. $em->flush(); // Executes all updates.
  67. $em->clear(); // Detaches all objects from Doctrine!
  68. }
  69. ++$i;
  70. }
  71. $em->flush();
  72. .. note::
  73. Iterating results is not possible with queries that
  74. fetch-join a collection-valued association. The nature of such SQL
  75. result sets is not suitable for incremental hydration.
  76. Bulk Deletes
  77. ------------
  78. There are two possibilities for bulk deletes with Doctrine. You can
  79. either issue a single DQL DELETE query or you can iterate over
  80. results removing them one at a time.
  81. DQL DELETE
  82. ~~~~~~~~~~
  83. The by far most efficient way for bulk deletes is to use a DQL
  84. DELETE query.
  85. Example:
  86. .. code-block:: php
  87. <?php
  88. $q = $em->createQuery('delete from MyProject\Model\Manager m where m.salary > 100000');
  89. $numDeleted = $q->execute();
  90. Iterating results
  91. ~~~~~~~~~~~~~~~~~
  92. An alternative solution for bulk deletes is to use the
  93. ``Query#iterate()`` facility to iterate over the query results step
  94. by step instead of loading the whole result into memory at once.
  95. The following example shows how to do this:
  96. .. code-block:: php
  97. <?php
  98. $batchSize = 20;
  99. $i = 0;
  100. $q = $em->createQuery('select u from MyProject\Model\User u');
  101. $iterableResult = $q->iterate();
  102. while (($row = $iterableResult->next()) !== false) {
  103. $em->remove($row[0]);
  104. if (($i % $batchSize) === 0) {
  105. $em->flush(); // Executes all deletions.
  106. $em->clear(); // Detaches all objects from Doctrine!
  107. }
  108. ++$i;
  109. }
  110. $em->flush();
  111. .. note::
  112. Iterating results is not possible with queries that
  113. fetch-join a collection-valued association. The nature of such SQL
  114. result sets is not suitable for incremental hydration.
  115. Iterating Large Results for Data-Processing
  116. -------------------------------------------
  117. You can use the ``iterate()`` method just to iterate over a large
  118. result and no UPDATE or DELETE intention. The ``IterableResult``
  119. instance returned from ``$query->iterate()`` implements the
  120. Iterator interface so you can process a large result without memory
  121. problems using the following approach:
  122. .. code-block:: php
  123. <?php
  124. $q = $this->_em->createQuery('select u from MyProject\Model\User u');
  125. $iterableResult = $q->iterate();
  126. foreach ($iterableResult AS $row) {
  127. // do stuff with the data in the row, $row[0] is always the object
  128. // detach from Doctrine, so that it can be Garbage-Collected immediately
  129. $this->_em->detach($row[0]);
  130. }
  131. .. note::
  132. Iterating results is not possible with queries that
  133. fetch-join a collection-valued association. The nature of such SQL
  134. result sets is not suitable for incremental hydration.