CODEWITHSUNDEEP
sqlperformancehibernatejpa
nerd
nerd

Reputation: 869

JPA performance improvement with reducing number of columns in query

I have a big table that has many columns. In different use cases I need to load different columns from this table, but usually I don't need all of the columns. So now I think I should select the necessary columns from this big table.

For example I have a simple POJO like UseCase1 and I use this named query:

SELECT NEW UseCase1(t.a, t.b, t.c) FROM MyBigTable t

In update statements I also update only a, b, c fields.

So the question is, this is a good solution, to improve performance if I can't change the table? And will this improve the performance? :)

Upvotes: 1

Views: 1637

Answers (2)

Henno Vermeulen
Henno Vermeulen

Reputation: 1535

You can also lazy-load individual non-relation fields (e.g. Strings) by using @Basic(fetch = LAZY), e.g. 

@Basic(fetch = LAZY)
private String description;

The table column for this field will not be loaded. Be careful that when you DO access the field that it will be lazy loaded with a single query which has the potential to become a different performance problem (N+1 select).

Upvotes: 0

ManuPK
ManuPK

Reputation: 11839

We had similar situations, and you can see the performance improvement using this technique in 2 cases.

  1. If your table has more than 40-50 columns and you want only 5-10 values to be selected.
  2. If your Bean defines a non-Lazy collection mapping. Then avoiding this property in the constructor will be huge saving.(This one worked for us tremendously)

Like @JB told, when we speak about performance nothing can be finalized without measuring it.These 2 point were my general experiences.

Upvotes: 1

Related Questions