Scalability problems with one2many_list widgets

[elver]

I have a huge number of books in my database and each references a language object. In the language object form I have a one2many_list widget displaying all the books in that language.

The problem is that this list is huge (thousands of books) and whenever I view a language record, it takes several seconds to load. This is a giant scalability issue. We've only got about 9000 books in the test database. Eventually we're hoping to have several million.

There does not seem to be any way to set a limit on how many books to display at any one time in a one2many_list widget.

[Borja López Soilán (NeoPolus) (borjals)]

Have you tried using the Koo client (Qt/KDE OpenObject client: https://launchpad.net/openobject-client-kde)? It is able to do dynamic paging, loading records as you scroll down, instead of loading them all at once.

Also take a look at the Postgres/SQL side: are your queries ("books on this language") using indexes? I think you should make sure that you have at least an index on the language field of your books.

You may enable Postgres logging to see which queries is doing OpenERP (and you can use pgFouine to analyze that log file for the most frequent queries, the slowest ones... http://pgfouine.projects.postgresql.org/).
Then use the explain feature of Postgres to know if those queries are using indexes "EXPLAIN SELECT..." (http://www.postgresql.org/docs/8.1/static/sql-explain.html). And then, if you discover a complex query that requires indexes, you may extend the autoinit of your book object to create the indexes you need ("cr.execute('CREATE INDEX...") when the object is load (search for "CREATE INDEX" on the addons to find some examples)

[elver (elver-loho)]

While it's a lot faster with the index, in the current worst case (4260 records displayed total in two one2many_list widgets) the delay is still noticeable. This solution might not scale well to cases where there might be 20k-30k records in the list.

Therefore I consider the lack of an implicit or explicit limit on the number of records a one2many_list displays to be a scalability problem.

[Borja López Soilán (NeoPolus) (borjals)]

Have you tried with the Koo client?
The GTK and Web clients will read all the records data when showing the one2many_list (so it will read those 4260 books).
The Koo client will read only the first records data when showing the widget (only loads data for the visible records), and won't read more records data until you scroll down.

Maybe the GTK and Web clients should implement such a feature (dynamic paging on lists).

[elver (elver-loho)]

As far as I've understood, the GTK client is currently the de facto client and it's also what my system's users are and will be using on their Windows workstations. If this is a GTK client bug -- and so it seems to be -- then can we transfer this bug report to the relevant bug list?

[Borja López Soilán (NeoPolus) (borjals)]

More than a bug, it is a known limitation.

But I'm with you: it it should be 'fixed' on GTK and Web clients, as it limits their scalability a lot. (It may be difficult to fix on the web client though)

Working with the KDE client is a lot of faster and way more comfortable thanks to dynamic paging:
   - Lists load a lot of faster (both form views in tree mode and one2many_list).
   - You can sort the list by any field, and ALL the records will get sorted, not just the ones on the current page.
   - You can just scroll down all way to the last record, no need to push "next page" again and again.

[Ferdinand (office-chricar)]

Koo performance revision 1265 really rocks

[Ferdinand (office-chricar)]

nevertheles there are still some issues with sorting ( function fields - no sorting possible)

[Jay Vora (Serpent Consulting Services) (jayvora)]

I would like to close the bug as the post does not lead to a specific information.
Please open another bug if you find any really useful issue.
Thanks everyone who participated here.