JPA performance tip – 1: surrogate keys.
This post is the first in series of JPA performance tips.
The tips are result of my working with JPA and facing issues which are not documented.
These are non-obvious performance pitfalls which if you do not take care from beginning may result in lot of rework or worse non resolvable issues making you grin and bear.
So, please use my experience and build your systems correctly from the beginning.
Surrogate key: The surrogate key is the primary key with no business meaning. Generally the surrogate key type of primary key is of two types:
- Usually generated with database sequence (but not always)
- GUID generator.
The primary key generated with sequence is of type Number (translating to long in java).We will discuss how a ‘long’ type of primary key can improve the performance in a database.
Let us take an example tables
‘Person’ : contains the details of a person
‘Person_phones’ : One or more phones for a user
The Person entity class is created as follows
The Person_phones class is:
To create the person and person phone following code snippet is given. The id in both person and person_phone table is assigned using setter methods.
Here the CRUD operations are performed using the CRUDRepository interface provided by spring.
If we run the code with show_sql true lets see the output
<!– HTML generated using hilite.me –>
We see that there is a Select query before an insert !!!!!
Now if the person and person_phone contains millions of records, consider the cost of one extra select query. But the point to explore is why did this happen ??
For getting the answer, we need to go into the code of CRUDRepository by Spring. When we call the repository.save() the control goes to the following class and code snippet
The entityInformation.isNew(s entity), checks the primary key type. if the primary key type is primitive, i.e. long, int etc it returns true and e.persist is called.
In case pf PK being String the String (as in our case) or any other class (as in composite key) the entityInformation.isNew(s entity) returns false and em.merge() called.
In case of em.merge(), the hibernate checks if the entity is already existing by making the select query. Hence we get select query in above example.
if the primary key is primitive and surrogate (generation type auto, sequence or table) i.e. the not the application data, the em.new() is called and and there is only insert query saving on valuable IO.
Conclusion: Always have surrogate keys for all tables. The relationship (oneToMany, manyToOne) can be maintained outside the primary keys. The non-primitive primary keys and composite keys will have a performance impact while inserting new rows.
This article can also be viewed at https://bootcamptechblog.wordpress.com/2015/09/25/jpa-performance-tip-1-surrogate-keys/