[Erp5-dev] some missing methods

[Yoshinori Okuji]

These Z SQL Methods are missing:

- z_catalog_delivery_list

- z_catalog_predicate_list

- z_catalog_predicate_category_list

Because of this bug, the hot reindexing does not work correctly.

YO

[Sebastien Robin]

Post by Yoshinori Okuji
- z_catalog_delivery_list
- z_catalog_predicate_list
- z_catalog_predicate_category_list

I have added the predicate table and predicate_category table. So I can
fix.

I have a question, may be it is a bit stupid, but I would like to
undestand why we need to create a z_xxx_list zsql method.

If I'm correct, I guess we use z_xxx_list in order to catalog a list of
objects by the same time, so instead of calling mysql 100 of times, we
will call it only 1 time.

So we may have only one query like this (case 1):
insert into catalog values
(a,b,c),
(d,e,f),
(g,h,i);

Here is my question : isn't it possible to only use the z_xxx method
(without list), take the output of the method on each object without
calling mysql, then join the output and then call mysql ? we should have
something like this (case 2):
insert into catalog values (a,b,c);
insert into catalog values (d,e,f);
insert into catalog values (g,h,i);

In both cases, we call mysql only one time, and in the second case we
don't have to write a z_xxx_list zsql method.

Isn't it possible to do the second case ? Is there an import perfomance
difference ?

I guess it is important that everyone understand why use use z_xxx_list
zsql method, so any explanation will be welcome.


Seb.

Post by Yoshinori Okuji
Because of this bug, the hot reindexing does not work correctly.
YO

[Yoshinori Okuji]

Post by Sebastien Robin
I have a question, may be it is a bit stupid, but I would like to
(without list), take the output of the method on each object without
calling mysql, then join the output and then call mysql ? we should have
insert into catalog values (a,b,c);
insert into catalog values (d,e,f);
insert into catalog values (g,h,i);
In both cases, we call mysql only one time, and in the second case we
don't have to write a z_xxx_list zsql method.
Isn't it possible to do the second case ? Is there an import perfomance
difference ?

As you guess, the performance changes a lot.

This is an example. First, I make a dump of Nexedi ERP5 without extended
inserts:

[yo at localhost test]$ mysqldump -u root nexedi_erp5 > standard.sql

Then, insert it into another database:

[yo at localhost test]$ time mysql test < standard.sql
0.79user 0.44system 3:12.21elapsed 0%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (0major+373minor)pagefaults 0swaps

With extended inserts:

[yo at localhost test]$ mysqldump -u root -e nexedi_erp5 > extended.sql

Insert it into a database:

[yo at localhost test]$ time mysql test < extended.sql
0.20user 0.01system 0:07.77elapsed 2%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (0major+901minor)pagefaults 0swaps

So using extended inserts is 25 times faster in this test. I heard that Coramy
ERP5 was indexed only for 7 minutes with extended inserts, while reindexing
it without extended inserts took several hours, according to Jean-Paul.

I think this difference is mainly because of rebuilding of indexes. If each
row is inserted one by one, MySQL must reindex the rows every time. If a
number of rows are inserted at one shot, MySQL does reindex much fewer times.

That's why we are interested in reindexing a document with all sub-objects,
even when only one sub-object is modified. There is a possibility that this
can be faster than reindexing some objects, although I suspect that this does
not perform well, due to the overhead of the evaluation of DTMLs.

YO