Deleting a page with a 3-level nested repeater takes **8x longer** than deleting the repeater items first then deleting the page

[jlahijani]

I have template called order. It has a repeater field called fulfillment_orders. That repeater field has a repeater field called fulfillments. That repeater field has a repeater field called fulfillment_line_items.

(If you're wondering, it's similar to parts of Shopify's order model).

I've made a bunch of test order pages that have the exact same data.

If I delete a page using $p->delete(), it takes 33 seconds (ouch!).

foreach($pages->find("parent=/orders/,template=order,limit=1") as $p) {
  $p->delete();
}

This was really bugging me, so I did tried a bunch of things. Turning off debug mode makes it 25 seconds (better). That was the only setting that made a difference. Removing all hooks didn't have an impact.

I then tried deleting the contents of fulfillment_orders repeater first then deleted the page. It takes 8 seconds (3x faster)!

foreach($pages->find("parent=/orders/,template=order,limit=1") as $p) {
  $p->fulfillment_orders->removeAll();
  $p->save(['fulfillment_orders']);
  $p->delete();
}

If I go another 2 levels deep and delete the contents of the fulfillments repeater field inside each fulfillment_orders repeater field, then delete the contents of the fulfillment_orders repeater field, it takes 3 seconds!

foreach($pages->find("parent=/orders/,template=order,limit=1") as $p) {
  foreach($p->fulfillment_orders as $fulfillment_order) {
    foreach($fulfillment_order->fulfillments as $fulfillment) {
      $fulfillment->fulfillment_line_items->removeAll();
      $fulfillment->save(['fulfillment_line_items']);
    }
    $fulfillment_order->fulfillments->removeAll();
    $fulfillment_order->save(['fulfillments']);
  }
  $p->fulfillment_orders->removeAll();
  $p->save(['fulfillment_orders']);
  $p->delete();
}

Why is it so much faster that way and can deleting a page with nested repeaters as demonstrated in my first code example be just as fast as my third code example? Seems like there's something going on?

Note: I'm using InnoDB by the way.

[elabx]

Have you tried putting all of these in one transaction @jlahijani ? Although 33 seconds does sound like way too much.

[jlahijani]

@elabx If I remember correctly, doesn't ProcessWire already do that for you? About 6 months ago, I was looking into deletion performance and I believe I tried doing that but doing so errored because a transaction is already in-place with ProcessWire natively.

If I'm mistaken, please let me know some test code I can run.

[elabx]

@jlahijani I see transactions implemented in a few places in the core, but I think in general when doing these delete operations is up to the us to handle this. I am not super experienced in this so please take it with a grain of salt, but I remember a couple years ago that using the transaction methods in $database did make a big CSV import I was making way faster.

Maybe look into db indexes?? The time you mention does seem like the case of a missing index, which is the only time I've seen these ridiculous times for database operations.

[jlahijani]

Does this commit address this issue? https://github.com/processwire/processwire/commit/19fb83201d599ff5fbd7634abf52f17b6a7ec30d

[romaincazier]

@jlahijani Ryan did this edit following a similar issue raised on the ProFields forum. Did the commit solved your issue as well?

[jlahijani]

@romaincazier Yes it looks like this did in fact resolve the issue. :) Thanks @ryancramerdesign