poor Hibernate select performance comparing to running directly - how debug?
Thank you all for help. After long time of struggling with that issue, finally kaliatech answer helped me to debug the problem.
First of all, I've made a terrible mistake in my quesion. I wrote that:
Running this query with local database is really fast, but using it remotely is really poor.
As it is not completly true. The query which I did in Hibernate looks like the one up:
select s.* from sales_unit s left join sales_unit_relation r on (s.sales_unit_id = r.sales_unit_child_id) where r.sales_unit_child_id is null
But the actual query which I did with SQL PLus or Navicat for example was:
select * from sales_unit s left join sales_unit_relation r on (s.sales_unit_id = r.sales_unit_child_id) where r.sales_unit_child_id is null
Please notice that first query select starts: select s.* ...
and second one is select * ...
. And that was the reason of such poor performance. Now both queries are completed in no time. The question is, what's the difference: performance issue: difference between select s.* vs select *
Hibernate JPQL query is very slow compared to SQL
Finally I found a solution, problem was in another part of code.
Before calling method getLinkedPerson() I had this line of code:
List<CNPerson> cnPersonList = cnPersonDao.findCnPersonNotLinkedWithPerson(obiekt.getLoid());
cnPersonList constans here about 70 000 objects.
I changed it to:
List<Integer> ids = cnPersonDao.findCnPersonIdsNotLinkedWithPerson(obiekt.getLoid());
Problem is described here: https://stackoverflow.com/a/46258045/9678458
Slow down during Hibernate context refresh. In case when you update
too many objects ORM engine (lets say Hibernate) has to sink them and
keep them in memory. Literally Hibernate must have all old states and
all new states of updated objects. Sometimes it does this quite
unoptimal.You can indicate this using debug. Try to find the slowest place and
check what exactly is being invoked there. I might guess that it slows
down when hibernate updates state of the cache.
I think it is because entities CNPerson and PersonLinked are linked, but I am not sure:
@ManyToOne(fetch = FetchType.LAZY,
cascade = {CascadeType.MERGE, CascadeType.PERSIST})
@JoinTable(name = "cnperson_links",
joinColumns = {@JoinColumn(name = "cnperson_loid")},
inverseJoinColumns = {@JoinColumn(name = "person_id")})
private PersonLinked personLinked;
Related Topics
How to Update an Entity Using Spring-Data-Jpa
Why Can Final Object Be Modified
Integer Arithmetic in Java with Char and Integer Literal
How to Find Out What Keystore My Jvm Is Using
Java - How to Receive Point Coordinates After Mouse Button Release (Jfreechart)
How to Get Whole and Fractional Parts from Double in Jsp/Java
How to Add a New Sourceset to Gradle
How to Include SQLite Database in Executable Jar
How to Obtain the Last Path Segment of a Uri
How to Get the Current Date and Time of Your Timezone in Java
Efficiently Compute Intersection of Two Sets in Java
What Is the Default Max Heap Size (-Xmx) in Java 8
How to Serve Jsps from Inside a Jar in Lib, or Is There a Workaround
What Is the Best/Simplest Way to Read in an Xml File in Java Application