Speed up Propel nested-set inserts building large tree

I'm using Propel 1.6.7-dev, and am maintaining 2 trees in a nested set (each with a different scope column). Currently, we have about 20K items in each set (so ~40k records total).

I can rebuild the database at will, and periodically do (to handle certain large-tree changes).

Right now, this is taking about 50m or so to accomplish, and I'm wondering how I might be able to speed it up (if this doubles in size, the time might become too large rebuild regularly).

Right now, I'm:

  • clearInstancePool();
  • Propel::disableInstancePooling();
  • beginTransaction();
  • (code to re-insert all items into the 2 trees)
  • commit();
  • enableInstancePooling();

I've experimented with changing the engine from MyISAM to MEMORY, build tree, then convert back to MyISAM. However, I run out of memory:

PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 72 bytes) in vendor/propel/runtime/lib/adapter/DBAdapter.php on line 433

Any suggestions?

Answers


The nested set algorithm relies heavily on WHERE clauses on "left" and "right" columns, to maintain the correct structure of your tree. If your queries are getting progressively slower as you have more nodes, the solution may be to add indexes on these columns.


Need Your Help

Jquery .click event handler not working for class selector in CHROME

jquery html css eventhandler

When I try to set the same opacity as mouseover when a .click event occurs, it doesn't work.

Resize JScrollPane client without scrollbars rolling

java swing client size jscrollpane

I have a JScrollpane which contains a scrollable client that changes its size dynamically while using the application. I want the JScrollPane to be aware of the changes without moving the viewport ...