On 13/03/11 14:38 +0100, Mathias Behrle wrote: > * Betr.: " Re: [tryton-dev] Performance and MPTT-Update" (Thu, 10 Mar 2011 > 15:23:36 +0100): > > > On 10/03/11 15:17 +0100, Bertrand Chenal wrote: > > > Le Thu, 10 Mar 2011 14:31:04 +0100, > > > Cédric Krier <[email protected]> a écrit : > > > > > > > > > > > > > Another solution is to drop mptt and use a closure table[1], it > > > > > seems to generate far less queries to maintain the index. > > > > > > > > Less query but more rows so is it really faster? > > > > > > > > > > Probably yes, > > > (1) queries are expensive (each one add a round-trip to > > > Posqtgresql), > > > > But the closures queries create much more records which cost also. > > > > > (2) when using closure table most of the work is made by > > > Postgresql and save work in python (and C code is several magnitude > > > quicker than python), > > > > I don't think it is the Python code of MPTT that is slow. > > The design by itself is slow.
It is not the design that is slow but the implementation. After a quick look, I already see how to remove 2 queries and I think it is possible to remove the loop using IN_MAX. > The scenario I built with only 2000 nested account views shows already the big > performance problem of an ad hoc actualization of the tree. Just deleting one > account from this simple tree takes 5-6 sec (depending of course from the > hardware). Imagine a big warehouse with hundreds of shelves, thousands of > sections... it just won't work. It is really marginal cases. It should almost never append that you must delete locations or accounts (just use the logical deletion). > I am strongly adhering to the proposal to rebuild the tree on demand (i.e. the > first search running on a dirty tree will rebuild it), thus saving a lot of > unnecessary tree updates. I already explain that you can not do it this way because of transaction. Also, there will be a lot of risk to have concurency update when running search. And rebuilding a complete tree is far more time consuming than just update it. > Perhaps a way of rebuilding a dirty tree in the > background in times of low load could be found? Same issue than above with one more that is what happens if search is done before rebuild. > I am asking once again in this context for the inclusion of the patch I > proposed, whether documented or undocumented (the responsibility of the > developer could be pointed out, that he is in charge to take the correct > measures and of rebuidling the tree). The patch is currently just a life-saver > in everyday practical life. We had to interrupt account chart installations > taking more than one day, because the postgres was lost in heavy transaction. > With the patch the installation took 15-20m min. For us it is essential to > have > this switch, at least until there will be a more performant design. I'm strongly against the inclusion of dirty hack in the framework. This is not the way to build a good, stable and reliable software. Performence should not be an excuse. After all, Business software is all about data integrity and your patch gives so much possibilities of breaking this integrity. If you really don't like MPTT, you can deactivate it by removing left/right attributes on parent field. Or help on improving the _update_tree method, there is a large unittest on this method that will prevent regressions. -- Cédric Krier B2CK SPRL Rue de Rotterdam, 4 4000 Liège Belgium Tel: +32 472 54 46 59 Email/Jabber: [email protected] Website: http://www.b2ck.com/
pgpcpfVaxTYaR.pgp
Description: PGP signature
