CODEWITHSUNDEEP
javaspringspring-bootspring-data
pepote
pepote

Reputation: 313

CrudRepository findAll() takes way to long - alternative? increase performance?

In my controller im using the CrudRepository method findAll() to find all users in my Database like this:

userRepository.findAll()

The Problem is that in doing so it takes at leat 1.3 Minutes to load 1.500 users. From there i load the data in a model with Thymeleaf i show it in an html table: name,timecreated,email,id,packet and status of each user. Is there any way to increase performance or a way around to my problem? Any help would be really apreciated.

Here´s my user entity 

    @Id
    @SequenceGenerator(name = "user_id_generator", sequenceName = "user_id_seq", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "user_id_generator")
    private Long id;
    @Column(nullable = false, unique = true)
    private String email;
    @Column(name = "uuid", nullable = false, unique = true)
    private String uuid;
    @Column(name = "reset_pwd_uuid", unique = true)
    private String resetPwdUuid;
    @Column(nullable = false)
    private String password;
    @Enumerated(EnumType.STRING)
    @Column(nullable = false)
    private Status status;
    @Enumerated(EnumType.STRING)
    @Column(nullable = false)
    private Packet packet = Packet.BASE;
    @Enumerated(EnumType.STRING)
    @Column
    private Situation situation;
    @Column(nullable = false, name = "numberOfSomething")
    private Integer apples;
    @Column(nullable = false, name = "numberOfSomethingElse")
    private Integer oranges;
    @Column(name = "time_created")
    private Timestamp timeCreated;

    @OneToMany(mappedBy = "user", cascade = {CascadeType.REMOVE})
    @LazyCollection(LazyCollectionOption.FALSE)
    @OrderBy("rank ASC")
    private List<Person> person;

    @OneToMany(mappedBy = "user", cascade = {CascadeType.REMOVE})
    @LazyCollection(LazyCollectionOption.FALSE)
    @OrderBy("timeOccured ASC")
    private List<History> history;

    @OneToMany(mappedBy = "user", cascade = {CascadeType.REMOVE})
    @LazyCollection(LazyCollectionOption.FALSE)
    @OrderBy("id ASC")
    private List<Invoice> invoices;

    @OneToOne(mappedBy = "user", cascade = {CascadeType.REMOVE})
    private Building building;

    @OneToOne(mappedBy = "user", cascade = {CascadeType.REMOVE})
    private House house;

    @OneToOne(mappedBy = "user", cascade = {CascadeType.REMOVE})
    private Car car;

    @OneToOne(mappedBy = "user", cascade = {CascadeType.REMOVE})
    private Street street;

    @OneToOne(mappedBy = "user", cascade = {CascadeType.REMOVE})
    private Moreof moreof;

    @JoinColumn(name = "basedata_id", referencedColumnName = "id")
    @ManyToOne(cascade = {CascadeType.REMOVE})
    private Basedata basedata;

    @Column(name = "family")
    private String family;

    @Column(name = "unkle_mail")
    private boolean unkleMail;

    @Column(name = "vacation")
    private LocalDate vacationUntil;

    @Column(name = "ordered")
    private boolean ordered;

    @Column(name = "shipped")
    private boolean shipped;

    @Transient
    private boolean isEdit;

    @Transient
    private boolean one;
    @Transient
    private boolean two;
    @Transient
    private boolean three;
    @Transient
    private boolean four;
    @Transient
    private LocalDate regDate;

    @OneToMany(fetch = FetchType.EAGER, mappedBy = "user", cascade = CascadeType.REMOVE)
    private List<Bed> bed;

Upvotes: 3

Views: 5525

Answers (1)

Amer Qarabsa
Amer Qarabsa

Reputation: 6574

loading 1500 user should not take that long, what is taking long is loading the association.

Problem

By default in JPA any toMany relation is lazy loaded, which means along with your entity you have a proxy to the collection and the collection is really loaded on the first access, so in that case fetching your 1500 user should not take that long.

In your case you are disabling the lazy loading by either specifying the fetch to be eager on the jpa level or using @LazyCollection(LazyCollectionOption.FALSE) on hibernate level, this is not recommended and expected to have performance issues.

Solution

Obviously the solution will be to not disable lazy loading

Upvotes: 9

Related Questions