N+1 Hibernate

[thorn olive]

⌛ This post has been reserved for your question.

Hey @vagrant zodiac! Please use /close or the Close Post button above when your problem is solved. Please remember to follow the help guidelines. This post will be automatically marked as dormant after 300 minutes of inactivity.

TIP: Narrow down your issue to simple and precise questions to maximize the chance that others will reply in here.

[tranquil pilot]

wait

I saw that one

https://stackoverflow.com/staging-ground/79261263 LMAO

Do you need to access positions of the employees after the findAll()?

[vagrant zodiac]

nope only when i'm fetching all peoples

[vagrant zodiac]

not found

[tranquil pilot]

You have this in the Employee class:

    @OneToMany(mappedBy = "employee")
    private List<Position> positions = new ArrayList<>();

[tranquil pilot]

it's your Stack Overflow post so only you and reviewers can see it - you need to be logged in to Stack Overflow

[tranquil pilot]

(k4rol4)

[tranquil pilot]

try using lazy loading there

[vagrant zodiac]

lol, wife account 😄

[tranquil pilot]

so @OneToMany(mappedBy = "employee", fetch = FetchType.LAZY)

[vagrant zodiac]

w8

[tranquil pilot]

actually that should be the default

Is the second SQL statement in the logs occuring multiple times?

[tranquil pilot]

😂

[tranquil pilot]

if not, where exactly do you have the N+1 problem?

[vagrant zodiac]

still the same result 2 select Hibernate: select distinct p1_0.id,p1_0.dtype,p1_0.email,p1_0.height,p1_0.name,p1_0.person_number,p1_0.surname,p1_0.type,p1_0.version,p1_0.weight,p1_0.pension_amount,p1_0.worked_years,p1_0.scholarship_amount,p1_0.specialization,p1_0.university_name,p1_0.year_of_study from person p1_0 where 1=1 offset ? rows fetch first ? rows only Hibernate: select p1_0.employee_id,p1_0.id,p1_0.end_date,p1_0.position,p1_0.salary,p1_0.start_date from position p1_0 where p1_0.employee_id=?

[tranquil pilot]

2 SELECTs is not an N+1 problem on its own

What exactly is the issue you are facing?

(would be good to add to the SO post as well)

[vagrant zodiac]

hm, I'v got n+1 problem when i'm going to fetch all people, when I'v got alien, kid, retiree in database everything is ok, but when i'v got employee with position(other entity) i'v got n+1

[tranquil pilot]

Are you getting the same SQL statement multiple times?

How exactly do you recognize an N+1 problem here?

And are the repeated queries executed during the findAll() call or are they executed afterwrds

[vagrant zodiac]

yes, I have only one endpoint to fetch all people - and sorting/filtering,

[tranquil pilot]

Can you show the result of that endpoint?

[vagrant zodiac]

sure

[tranquil pilot]

Does the endpoint include positions for employees?

[vagrant zodiac]

    "content": [
        {
            "id": 1,
            "name": "Iza",
            "surname": "Izabelowa",
            "personNumber": "2434567890",
            "weight": 27.0,
            "height": 222.0,
            "email": "[email protected]",
            "type": "Employee",
            "version": 0,
            "positionCount": 0
        },
        {
            "id": 2,
            "name": "Anna",
            "surname": "Nowak",
            "personNumber": "9876543210",
            "weight": 160.0,
            "height": 65.0,
            "email": "[email protected]",
            "type": "Retiree",
            "version": 0,
            "pensionAmount": 3000.5,
            "workedYears": 35
        }
    ],
    "pageable": {
        "pageNumber": 0,
        "pageSize": 40,
        "sort": [],
        "offset": 0,
        "paged": true,
        "unpaged": false
    },
    "last": true,
    "totalElements": 2,
    "totalPages": 1,
    "size": 40,
    "number": 0,
    "sort": [],
    "numberOfElements": 2,
    "first": true,
    "empty": false
}```

[vagrant zodiac]

only position count

its about how many positions he have

[tranquil pilot]

How are you computing the position count?

[vagrant zodiac]

in toDto method Employee employee = (Employee) person; employeeDto.setPositionCount(employee.getPositions() != null ? employee.getPositions().size() : 0);

[tranquil pilot]

Ok I think the employee.getPositions().size() requests the SELECTs on the positions

In the

    @OneToMany(mappedBy = "employee")
    private List<Position> positions = new ArrayList<>();

of the Employee class, can you add fetch = FetchType.EAGER? Does that change the SQL statements?

and ideally also try it out with at least 3 employees

[vagrant zodiac]

w8 🙂

Hibernate: select p1_0.employee_id,p1_0.id,p1_0.end_date,p1_0.position,p1_0.salary,p1_0.start_date from position p1_0 where p1_0.employee_id=?
Hibernate: select p1_0.employee_id,p1_0.id,p1_0.end_date,p1_0.position,p1_0.salary,p1_0.start_date from position p1_0 where p1_0.employee_id=?
Hibernate: select p1_0.employee_id,p1_0.id,p1_0.end_date,p1_0.position,p1_0.salary,p1_0.start_date from position p1_0 where p1_0.employee_id=?
Hibernate: select p1_0.employee_id,p1_0.id,p1_0.end_date,p1_0.position,p1_0.salary,p1_0.start_date from position p1_0 where p1_0.employee_id=?
Hibernate: select p1_0.employee_id,p1_0.id,p1_0.end_date,p1_0.position,p1_0.salary,p1_0.start_date from position p1_0 where p1_0.employee_id=?
Hibernate: select p1_0.employee_id,p1_0.id,p1_0.end_date,p1_0.position,p1_0.salary,p1_0.start_date from position p1_0 where p1_0.employee_id=?```

[tranquil pilot]

alternatively you could try annotating positions with

@org.hibernate.annotations.LazyCollection(org.hibernate.annotations.LazyCollectionOption.EXTRA)

as explained in https://stackoverflow.com/a/2913876/10871900

[tranquil pilot]

yes, that's how you see the N+1 problem

[vagrant zodiac]

yeee..

[tranquil pilot]

Can you try that?

[tranquil pilot]

What fetch type did you use there?

[vagrant zodiac]

w8

im trying right now

[tranquil pilot]

My guess is having one COUNT() statement per element

[vagrant zodiac]

Hibernate: select count(id) from position where employee_id=?
Hibernate: select count(id) from position where employee_id=?
Hibernate: select count(id) from position where employee_id=?
Hibernate: select count(id) from position where employee_id=?
Hibernate: select count(id) from position where employee_id=?
Hibernate: select count(id) from position where employee_id=?
Hibernate: select count(id) from position where employee_id=?```

ugh, it's crazy

[tranquil pilot]

ok that's at least better than before

I guess your wife would be ok with me editing their SO post to include the details you provided and publish it?

[vagrant zodiac]

hahahahah! Sure!

[tranquil pilot]

so it's ok if these are included under the CC-BY-SA license from Stack Overflow?

[vagrant zodiac]

hm, what is CC-BY-SA license?

[tranquil pilot]

I don't know how to reduce that - you can try using views for that

[tranquil pilot]

All content on Stack Overflow is under the CC-BY-SA license which allows anyone to irrevokably use it if they state their changes, attribute (cite) it properly and use the same license
License text: https://creativecommons.org/licenses/by-sa/4.0/
Stack Overflow info: https://stackoverflow.com/help/licensing

[vagrant zodiac]

Hibernate: select distinct p1_0.id,p1_0.dtype,p1_0.email,p1_0.height,p1_0.name,p1_0.person_number,p1_0.surname,p1_0.type,p1_0.version,p1_0.weight,p1_0.pension_amount,p1_0.worked_years,p1_0.scholarship_amount,p1_0.specialization,p1_0.university_name,p1_0.year_of_study from person p1_0 where 1=1 offset ? rows fetch first ? rows only

look, i commented this counting line, and result is ok, now how to count employee position without n+1 problem 😄

[tranquil pilot]

You could try to create a view on the person table that includes all fields you need

so kind of a virtual table in the database and an entity for that virtual table but requesting that entity will do a request to the virtual table

[vagrant zodiac]

my view tables looking like that:

@Setter
@AllArgsConstructor
@NoArgsConstructor
@ToString
@SuperBuilder
public abstract class PersonDto {
    private int id;
    private String name;
    private String surname;
    private String personNumber;
    @NotNull
    private double weight;
    @NotNull
    private double height;
    private String email;
    private String type;

    private Long version;
}@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@SuperBuilder
@ToString
public class EmployeeDto extends PersonDto {
    private int positionCount;
}
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@SuperBuilder
public class RetireeDto extends PersonDto {
    private Double pensionAmount;
    private Integer workedYears;
}
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@SuperBuilder
public class PositionDto {
    private Integer id;
    private String position;
    private LocalDate startDate;
    private LocalDate endDate;
    private Double salary;
}

[tranquil pilot]

and in that view, it might be possible to change the positions field to an int with the count

[vagrant zodiac]

but now in EmployeeDto I dont have counting methot how many positions he have

[tranquil pilot]

yeah these are the DTOs and not the actual views

[vagrant zodiac]

intresting how i can do that?

[tranquil pilot]

https://stackoverflow.com/a/59979203/10871900

I don't know how to do that with inhertiance to be honest

which is why I think it would be a good SO question

[tranquil pilot]

If you are ok with everything from that channel being licensed under that, I can add that to the question (though written in a more compact form but still keeping what the question is asking)

[vagrant zodiac]

but I think now It's not needed I'm going to delete this post.

[tranquil pilot]

that works as well

but I don't know how to use views to do that without running into N+1

[vagrant zodiac]

I'm looking for best option to count this positions and put to EmployeeDto

[tranquil pilot]

I do think it would be a valid SO question but it's your choice if you don't want it

[tranquil pilot]

I was able to get you to that point which should be better than before and I think it should be possible to improve it with views but I don't know how

[lofty goblet]

hi guys

time to add my few messages too

l0l

[tranquil pilot]

wow

[lofty goblet]

you could use the left join clause

and that basically solves it

when fetching

[tranquil pilot]

and how would you do that with Spring JPA and entity inheritance as in this case?

[lofty goblet]

or just eager fetching if you will always need the association

let me look at the entity uno momento

[tranquil pilot]

eager fetching alone isn't necessarily the issue here

and note that only the COUNT of the positions per employee is of interest (but of all requested employees)

[vagrant zodiac]

now it's solved we got another questions, like how to count positions and add to EmployeeDto -> without N+1

[vagrant zodiac]

yep

[lofty goblet]

ok so basically

if you are using eager fetch

you can just stream through the positions

and put them in a dto

[tranquil pilot]

then it would request all positions

[lofty goblet]

yea but theres no n plus 1

its all fetched with one query

[tranquil pilot]

positions are in a child class of Person and also non-Employee entities are requested

I think I suggested eager fetching for that positions before

[vagrant zodiac]

like retiree, student, alien, kid

[vagrant zodiac]

yes

[lofty goblet]

yes you can just do eager fetching

and

to count the amount of employees

[vagrant zodiac]

with different params

[lofty goblet]

you just do in the employee class

return positions size

[lofty goblet]

is this some kind of a project you are doing or is it an exam

[vagrant zodiac]

project

[lofty goblet]

oh

and do you need to have file processing in it

[vagrant zodiac]

nope

[lofty goblet]

ok nvm

i thought i saw a similar project from a tutorial

[tranquil pilot]

that still requests all employees which is what we are trying to avoid

[lofty goblet]

why tho

[vagrant zodiac]

oh where?

[lofty goblet]

eager fetch pulls all the stuff with one request

[tranquil pilot]

As I said I could rewrite the SO question to ask for doing the thing with the count properly (or you could rewrite it)

[lofty goblet]

i mean if theres no option to have a query

with counting it

you gotta fetch it

and just count it

@vagrant zodiac can you try what i said

[tranquil pilot]

FYI I'm preventing the question from being published in its current state (unless another reviewer overwrites my choice which would be kinda weird)

[lofty goblet]

one of the courses i was on

had a similar thing

but not exactly like that

[vagrant zodiac]

yes right now i'm trying this

[lofty goblet]

yea it should work properly now

in the employee method you can just do

return positions.size

()

https://tenor.com/view/star-trek-code-review-shocked-surprised-omg-gif-4771093

heres a gif so work goes faster

l0l

[thorn olive]

Before your post will be closed, would you like to express your gratitude to any of the people who helped you? When you're done, click I'm done here. Close this post!.