I've a simple select query which return 200 rows. The query is iterated 1437 times. Technology : java 8, spring boot 2.1.3.RELEASE, tomcat, hibernate
At each iteration, the query becomes slower. The first query takes 55ms and the last query takes 702ms
However, when I start the same query in Junit "@RunWith(SpringJUnit4ClassRunner.class)" , the queries are not becoming slower. Every query takes +- 37ms
Log of first and last when running in Tomcat
Log of first and last when running junit
As you can see in the logs, one difference is that the Entity Manager is not closed after each iteration on Tomcat (but it is closed on JUnit). After 1k iterations, the entity manager will hold a lot of objects in memory and operations on such a loaded context become expensive. Memory pressure should also be higher and higher during each iteration.
I would try to clear the context more often (i.e. after every iteration) or at least increase the available memory to rule out GC coming into play too often.
See also this answer
I added entitymanager.clear() after each query, and this solved the problem. Thanks Cascader !!
The result is really impressive. The first query takes 73ms, and it goes the opposite way "down" to 1ms for the last query
