Seam + JPA performance problems

[securez]

I'm having a lot of trouble with performance problems, i'm a seam newbye and i d'nt know if i making the thinks in the riogth way.

My system is Jboss 4.2.2.GA and Seam 2.0.1.GA, Oracle database, Java 5.

I have a very complex database schema, some of the data are dynamic created with views, this views can't be altered, since it's client tables from other software.

In my application i ahve one table for example that have a lot of foreign key columns, that are many-to-one columns, the entity definition look like this:

@Entity
@Table(name="enlaceoba")
@PrimaryKeyJoinColumn(name="idoba")
public class EnlaceOba implements Serializable {
...
    @ManyToOne(fetch=FetchType.EAGER)
    @JoinColumn(name="idnodo1",referencedColumnName="id")
    public Nodo getNodoOrigen() {
        return nodoOrigen;
    }

    public void setNodoOrigen(Nodo nodoOrigen) {
        this.nodoOrigen = nodoOrigen;
    }
...
}

So when i click in edit button to edit the data, via this command:

...
nuevoCircuito = entityManager.find(EnlaceOba.class, idOBA);
...

I see in the log that a very long query is build, make a join of all the tables, so make that need to wait more than 30 sec to see the form.

In the form echa of this columns are combos in this form:

<h:selectOneListbox  id="nodoorigen" value="#{nuevoCircuito.nodoDestino}" size="1">
                    <s:convertEntity/>
                    <s:selectItems value="#{comun_listaNodosTelefonica}" var="_nodo" label="#{_nodo.descripcion}" noSelectionLabel="---"/>
</h:selectOneListbox>

So i don't understand why all the tables are joined, if when the combos are rendered a query of each table is performed to fill the list of values, why its joined with the main entity.

¿There is any form that only the mai ntable are queried?

I have this issue in some points on the application, i'm refactoring the app, so i'm created it, i'm not a seam expert, i spend some hours to try optimize it with no success, any help of light about the issue is apreciated.

Regards.

[diegocoronel]

Maybe its happening because you are probably using EAGER in  all  entities, this makes hibernate load all  your data(including OneToMany, ManyToOne). Using seam is possible to use LAZY and not worry about lazyInitializationExcepion, you should try some LAZY mapping to optimize you performance.

[dan.j.allen]

You should almost never use EAGER. A case for using EAGER would perhaps be an employee with multiple e-mail addresses where the collection of Email entities is virtually part of the Employee entity. If you want to do joins, they should be done in JPQL. Otherwise, you are just asking for trouble.

select e from EnlaceOba e join fetch e.nodeOrigin

[securez]

I change the model clasess to use LAZY loading for many-to-one relations that i have, so when i edit the data, ok a simple select is issued, but a lot of selects are issued to qet the object that will displayed selected in the selectOne JSF control.

My question is, why query this two times, the render of the selectOne will query all posible values from database, so query it again makes no sense.

How can i avoid this?

[dan.j.allen]

You need to use the fetch join that I proposed above. This would be done in a factory:

@Factory("enlaceObaList")
public List<EnlaceOba> fetchEnlaceObaList() {
    return entityManager
        .createQuery("select e from EnlaceOba e join fetch e.nodeOrigin")
        .getResultList();
}

Yes, it is going to do n+1 select if you don't use eager fetching on a query, but you don't want eager fetching on your entities.

[admin.admin.email.tld]

I'll agree with Dan on this.  Using the @Factory method and a JPA query with fetch join will solve your problem.  The @Factory method only gets executed if the value of @Factory is null.  Due to the nature of the JSF lifecycle, the getter/setter and action methods in the JSF EL that point to the SFSB are executed too many times in some cases, so you need to check for null, otherwise you'll end up with excessive processing and transactions (i.e. performance hits).

refer to section 13.2 Selecting a fetch strategy and pgs. 649-650 of Bauer and King book for more on when to use eager vs. lazy and fetch joins.

Here's the main point from section 13.2.5:

If you switch from the default strategy to queries that eagerly fetch data with joins, you may run into another problem, the Cartesian product issue.  Instead of executing too many SQL statements, you may now (often as a side effect) create statements that retrieve too much data.

You need to find the middle ground b/n the two extremes: the correct fetching strategy for each procedure and use case in your application.