Some context
While browsing my app in pre-production, I noticed that the homepage was starting to take a long time to load...
Investigating in my APM
I looked in Kibana's APM (Application Performance Management) and...
To help you better picture the DB schema :
Results
Even though FetchMode.JOIN doesn't work, at least the relations where I removed the fetchType=EAGER should now be LAZY and my N+1 problem should improve... Right ??? But nothing has changed 😶
So, we should explicitly set FetchType.LAZY on the relationship…
But I think a much more interesting question is: why, in the name of Jesus of Nazareth, doesn’t Spring Data generate a join for us??? We’ll still try both approaches to see what happens.
Setting FetchType.LAZY on the @OneToOne ❌
I hope I won't need to make Bytecode enhancement because it seems a bit overkill for my use case 🙃
💡 Learning moment: Defining a @OneToOne relationship on both sides when it’s not necessary is a bad idea because the non-owning side will always default to FetchType.EAGER, leading to a ton of N+1 issues 💀
Gladly, in my case, a solution to the N+1 problem is to remove the @OneToOne relationship annotation on Relation_1 in the Project model since we never query the project alongside Relation_1.
Or, alternatively, I could ensure the relation is properly fetched with a JOIN query — though this is less optimal because it will unnecessarily load the memory with unneeded Relation_1 information when fetching projects.
Kindly ask Spring Data to perform JOINs 🤗 (😠)
As we saw earlier, FetchMode.JOIN is useless with Spring Data because it internally uses the Criteria API, which doesn’t consider FetchMode (thank you Criteria API).
The solution is to use
Well, after all these twists and turns... the entity graph works! 🥹🥹🥹🥹🥹🥹🥹🥹🥹🥹
You didn’t think it was over, did you? 😊
Removing the relation1 property from projectModel means we no longer benefit from orphanRemoval 🥲 so some of my tests broke.
In the end, it was pretty quick to fix though.
Conclusion
Removing the reference to relation1 in ProjectModel helped eliminate the N+1 issues on one of my routes, N+1 which were solely caused by the EAGER @OneToOne on the non-owning side.
And the entity graph helped eliminate the N+1 issues on the other route 😍, which was plagued by the relationship with relation2 which wasn't done with a JOIN.
💡 Thinking back on how to debug SQL performance issues
To iterate: write SQL tests!!!!! It helps you iterate so much faster than by manually testing your app 😱
There is an initial cost to setup a test reproducing your issue but I swear it is worth it.
By enabling show-sql: true in the application.yml for the test environment, you can see the SQL queries that are being executed.
I hope you learned something from my optimization journey and had some fun as well 😄
If you have any questions ask in the comments I'll respond to the best of my capacities 😉
SOCIAL SHARE CARD GENERATOR