Problem with a huge database

[rayan]

Hello folks :-)

I have a roo-based enterprise application, with an underlying database, which I have reverse engineered (with the help of roo of course). One of the data base tables (lets name it tableA) contains several hundred of thousands of records :-) Another table (tableB) which is in relationship with tableA has only 5 recors. While accessing tableB, the response lasts much too long resulting in other "parts" of my application to fail, since they depend on the database response (though they run asynchronously).

I have tried to solve the problem by using Hibernate lazy-fetching mechanism. To do that I changed the code of the TableB_RooDB_Managed.aj in this way:

@OneToMany(mappedBy = "tableb", fetch = FetchType.LAZY)
private Set<TableA> Tableb.tableas;

I also have increased the maximum memory reserved for the JVM. The problem has been partly solved, since a response from the database is returned sometime. But since it lasts too long the other parts of my application dismiss (give up probably to an internal timeout, which is not set by me).

Further details may hel ypu to help me :

1. Roo-version: 1.1.0.RELEASE
2. Hibernate-version:3.5.5
3. mysql-version: 5.1.24

Any (efficient) help would be more than appreciated :-)

Cheers!
Rayan

[mikej]

I'm not familiar with Roo reverse engineering, so this is a general response.

- Be careful about editing the .aj files, since Roo manages those itself.

- How did your old application load the data so efficiently? Have you lost custom queries, index hints, etc.?

- You can alter the underlying query or write a service method with a custom query. You can still use the gift of Roo: calling TableA.entityManager() will give you a javax.persistence.EntityManager.

- Do you really need an instance of tableb to contain the full list of related tableAs (lazily loaded or not)? If not, can you just remove that side of the relationship (i.e., remove "tableas" from Tableb.java)?

[rayan]

Hello mikej,

thank you vm for your kind reply... Here are my comments:

1) I am aware that the aj-files are managed by roo and should not be modified, but sometimes this is "the only way" and is even all right. If roo notices the modifications it "adapts" accordingly the code.

2) Till now I have been experimenting with a small database. And it has just worked...

3) I am afraid that this solution wouldn't be "handy"... Any way I am unfortunately not very experienced with JPA.

4) The full list of related tables is contained in the .aj-file (and not in the java-file). I have though tried to omit them, but this did not help a lot (no noticeable improvement).

Plz help!

Rayan

I'm not familiar with Roo reverse engineering, so this is a general response.

1) Be careful about editing the .aj files, since Roo manages those itself.

2) How did your old application load the data so efficiently? Have you lost custom queries, index hints, etc.?

3) You can alter the underlying query or write a service method with a custom query. You can still use the gift of Roo: calling TableA.entityManager() will give you a javax.persistence.EntityManager.

4) Do you really need an instance of tableb to contain the full list of related tableAs (lazily loaded or not)? If not, can you just remove that side of the relationship (i.e., remove "tableas" from Tableb.java)?

[mikej]

Dunno then.

But you can turn on logging to figure out just what sql is being executed. Do something like this in persistence.xml (at least if you are using Hibernate's JPA provider):

<property name="hibernate.show_sql" value="true"/>

You might have to set your logging level to DEBUG. That will generate lots of messages, but they will include the sql for each executed query.