N + 1 when ID is string (JpaRepository)

Question

I have an entity with string id:

@Table
@Entity
public class Stock {

    @Id
    @Column(nullable = false, length = 64)
    private String index;

    @Column(nullable = false)
    private Integer price;

}

And JpaRepository for it:

public interface StockRepository extends JpaRepository<Stock, String> {
}

When I call stockRepository::findAll, I have N + 1 problem:

logs are simplified

select s.index, s.price from stock s
select s.index, s.price from stock s where s.index = ?

The last line from the quote calls about 5K times (the size of the table). Also, when I update prices, I do next:

stockRepository.save(listOfStocksWithUpdatedPrices);

In logs I have N inserts.
I haven't seen similar behavior when id was numeric.
P.S. set id's type to numeric is not the best solution in my case.

UPDATE1:
I forgot to mention that there is also Trade class that has many-to-many relation with Stock:

@Table
@Entity
public class Trade {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    @Column
    @Enumerated(EnumType.STRING)
    private TradeType type;

    @Column
    @Enumerated(EnumType.STRING)
    private TradeState state;

    @MapKey(name = "index")
    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(name = "trade_stock",
        joinColumns = { @JoinColumn(name = "id", referencedColumnName = "id") },
        inverseJoinColumns = { @JoinColumn(name = "stock_index", referencedColumnName = "index") })
    private Map<String, Stock> stocks = new HashMap<>();

}

UPDATE2:
I added many-to-many relation for the Stock side:

@ManyToMany(cascade = CascadeType.ALL, mappedBy = "stocks") //lazy by default
Set<Trade> trades = new HashSet<>();

But now it left joins trades (but they're lazy), and all trade's collections (they are lazy too). However, generated Stock::toString method throws LazyInitializationException exception.

Please post the whole Stock class. The SQL says select s.name, s.price from stock s where is the attribute name? — Simon Martinelli, Dec 01 '16 at 16:00
I removed `Hibernate:` prefix and, for example `as index_1_9_0` parts. — Boris, Dec 01 '16 at 20:13
I thought many-to-many is bidirectional even if it's on only 1 side. Anyway, I added it and it works worse. — Boris, Dec 01 '16 at 20:44
Is it possible that you're calling that `Stock::toString` after the entities have been detached or the EntityManager is closed? Also the message of the exception might give a better clue, not just it's type. — coladict, Dec 19 '16 at 15:36

malejpavouk · Accepted Answer · 2018-01-05T10:15:40.397

Related answer: JPA eager fetch does not join

You basically need to set @Fetch(FetchMode.JOIN), because fetch = FetchType.EAGER just specifies that the relationship will be loaded, not how.

Also what might help with your problem is @BatchSize annotation, which specifies how many lazy collections will be loaded, when the first one is requested. For example, if you have 100 trades in memory (with stocks not initializes) @BatchSize(size=50) will make sure that only 2 queries will be used. Effectively changing n+1 to (n+1)/50. https://docs.jboss.org/hibernate/orm/4.3/javadocs/org/hibernate/annotations/BatchSize.html

Regarding inserts, you may want to set hibernate.jdbc.batch_size property and set order_inserts and order_updates to true as well. https://vladmihalcea.com/how-to-batch-insert-and-update-statements-with-hibernate/

score 0 · Answer 2 · answered Dec 19 '16 at 20:41

However, generated Stock::toString method throws LazyInitializationException exception.

Okay, from this I am assuming you have generated toString() (and most likely equals() and hashcode() methods) using either Lombok or an IDE generator based on all fields of your class.

Do not override equals() hashcode() and toString() in this way in a JPA environment as it has the potential to (a) trigger the exception you have seen if toString() accesses a lazily loaded collection outside of a transaction and (b) trigger the loading of extremely large volumes of data when used within a transaction. Write a sensible to String that does not involve associations and implement equals() and hashcode() using (a) some business key if one is available, (b) the ID (being aware if possible issues with this approach or (c) do not override them at all.

So firstly, remove these generated methods and see if that improves things a bit.

score 0 · Answer 3 · answered Dec 20 '16 at 00:52

With regards to the inserts, I do notice one thing that is often overlooked in JPA. I don't know what Database you use, but you have to be careful with

@GeneratedValue(strategy = GenerationType.AUTO)

For MySQL I think all JPA implementations map to an auto_incremented field, and once you know how JPA works, this has two implication.

Every insert will consist of two queries. First the insert and then a select query (LAST_INSERT_ID for MySQL) to get the generated primary key.
It also prevents any batch query optimization, because each query needs to be done in it's own insert.

If you insert a large number of objects, and you want good performance, I would recommend using table generated sequences, where you let JPA pre-allocate IDs in large chunks, this also allows the SQL driver do batch Insert into (...) VALUES(...) optimizations.

Another recommendation (not everyone agrees with me on this one). Personally I never use ManyToMany, I always decompose it into OneToMany and ManyToOne with the join table as a real entity. I like the added control it gives over cascading and fetch, and you avoid some of the ManyToMany traps that exist with bi-directional relations.

N + 1 when ID is string (JpaRepository)

3 Answers3