CODEWITHSUNDEEP
javasqlitejpaeclipselink
Alexander Remesch
Alexander Remesch

Reputation: 623

Loading a large database table using eclipselink/jpa takes extremely long when using associations

I'm using JPA/EclipseLink 2.5.2 to load two database tables from a SQLite database. There are two tables: one named Dwellings with a PK dwellingId and some other fields and one named Households with a PK householdId, a FK dwelllingId and some other fields.

The persistence classes look something like this:

@ReadOnly
@Entity
@Table(name="Dwellings")
@NamedQuery(name = "Dwelling.findAll", query = "SELECT d FROM Dwelling d")
public class Dwelling implements Serializable {
     private static final long serialVersionUID = -2430404920797112159L;

    @Id
    private int dwellingId;

    @OneToOne(mappedBy="dwelling", optional=true, cascade=CascadeType.ALL)
    private Household household;

    (...)
}

and

@ReadOnly
@Entity
@Table(name="Households")
@NamedQuery(name="Household.findAll", query="SELECT h FROM Household h")
public class Household implements Serializable {
    private static final long serialVersionUID = -360339872479840811L;

    @Id
    private int householdId;

    @OneToOne(optional = false, fetch = FetchType.EAGER)
    @JoinColumn(name = "DwellingId")
    private Dwelling dwelling;

    (...)
}

Now, if I load the table dwellings containing ~900,000 rows without the associations enabled (with the commands below), the whole load will take about 10 secs.

TypedQuery<Dwelling> dwellingQuery = entitymanager.createQuery("select d from Dwelling d", Dwelling.class);
List<Dwelling> dwellings = dwellingQuery.getResultList();

If I enable the associations between the two tables (households containing ~800.000 rows), the load will take ages (I interrupted it after ~10 mins).

I put an index on the Households.dwellingId column.

What else could I try to speed up the lookup of the associations?

Upvotes: 0

Views: 386

Answers (1)

Szarpul
Szarpul

Reputation: 1571

Maybe it's associated with ORM n+1 performance problem.

Upvotes: 1

Related Questions