Decrease in fetch - speed (on the UI) with increase in db entries

[Maja]

Hi,

We have a large model and the UI that was generated by Roo became so slow it's unusable when the database got populated with many records.I decided to check if this is a Roo-Spring-Hibernate issue or something to do with our model...

Testing on the Petclinic example when the database is populated with 1 000 entries it takes less than a second to list them, 10 000 takes 4 seconds and 60 000 takes 16 seconds.
Obviously, the more entries there are in the database, the slower it gets. It keeps the same speed when switching between pages in the list page.

Is there any way to speed it up and keep it fast regardless of the volume of the data? We would really like to be able to use our UI...

Thank you for any help on this!

[clandestino_bgd]

We are facing the same problem.
Any hint from roo developers is much appreciated.

Milan

[mmartinez]

The poor performance is produced on entity list page doing pagination ?

[Maja]

Poor performance is on listing (List all entities...), both with and without pagination. Loading entity for an update gets slower with increase of data as well.

Any hints?

[mmartinez]

Yeah, activate the SQL debug to view executed selects.

[Maja]

Thanks, did that and basically on every request almost the whole model is loaded.
Some optimisations included removing unnecessary ModelAttributes from controller, removing the "collection" fields from the tables.

I tried to treat it as a n+1 selects hibernate problem but, even though it did generate less sql statements, it didn't help with the speed.

the query on the petclinic is:

2011-05-13 12:20:48,675 [http-8080-12] DEBUG org.hibernate.SQL - select pet0_.id as id2_, pet0_.name as name2_, pet0_.owner as owner2_, pet0_.send_reminders as send3_2_, pet0_.type as type2_, pet0_.version as version2_, pet0_.weight as weight2_ from pet pet0_
2011-05-13 12:20:53,827 [http-8080-12] DEBUG org.hibernate.SQL - select vet0_.id as id1_, vet0_.address as address1_, vet0_.birth_day as birth4_1_, vet0_.city as city1_, vet0_.email as email1_, vet0_.first_name as first7_1_, vet0_.home_page as home8_1_, vet0_.last_name as last9_1_, vet0_.telephone as telephone1_, vet0_.version as version1_, vet0_.employed_since as employed12_1_, vet0_.specialty as specialty1_ from abstract_person vet0_ where vet0_.dtype='Vet'
2011-05-13 12:21:00,111 [http-8080-12] DEBUG org.hibernate.SQL - select visit0_.id as id0_, visit0_.description as descript2_0_, visit0_.pet as pet0_, visit0_.version as version0_, visit0_.vet as vet0_, visit0_.visit_date as visit4_0_ from visit visit0_
2011-05-13 12:21:04,638 [http-8080-12] DEBUG org.hibernate.SQL - select visit0_.id as id0_, visit0_.description as descript2_0_, visit0_.pet as pet0_, visit0_.version as version0_, visit0_.vet as vet0_, visit0_.visit_date as visit4_0_ from visit visit0_ limit ?
2011-05-13 12:21:04,651 [http-8080-12] DEBUG org.hibernate.SQL - select count(visit0_.id) as col_0_0_ from visit visit0_ limit ?

Ideas? Thoughts?

[sylvain.laurent]

Instantiation of roo entities is more expensive than usual JPA entities because of bean wiring added by @Configurable. In some cases I reverted to plain JPA entities and DAO to improve performance.

[mmartinez]

The other entities list defined as @ModelAttribute at Controller are required for update.jspx and create.jspx.

[Maja]

Can you please tell me what is the correct way to revert to plain JPA entities?
Should I just delete the Entity_Roo_Configurable file? Or does it mean I have to get rid of all the stuff generated by Roo for that entity and just write it by hand, from scratch?

Thanks!

p.s. @mmartinez - Some of the ModelAttributes aren't used in create and update pages at all, and those that are, but are expensive to fetch for the list page (or not displayed on it) can be added manually in the controller when preparing the update or create form.
This one, actually, was the biggest UI speed-up of all those I tried so far.

[mmartinez]

Can you create an improvement task at JIRA ?