Why are my Hibernate `select` queries super slow?

I am using Hibernate in my Java based REST API. I am using HQL mainly because it is easy. Also for this project, I am loading data from the main table, its associated tables, their associated tables and so on. The reason is then in the app, I have all the data I need in a one JSON string instead of running multiple methods.

Now, take a look at the following database structure.

Let’s take a look at the code. I am going to run getAll method on Stock. It will return me all the stock data + its product data + product’s vehicle_model data+ product’s spare_part data

import beans.Stock;
import beans.TempStock;
import dao.SessionFactoryBuilder;
import java.util.Date;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

public class StockDAOImpl implements StockDAOInterface {

    private final SessionFactoryBuilder sessionFactoryBuilder = SessionFactoryBuilder.getInstance();

    @Override
    public List<Stock> getAllStock(Session session) {
        Query query = session.createQuery("FROM Stock s");
        List<Stock> list = (List<Stock>) query.list();
        return list;
    }

}

I am just posting the data grab code.

Hibernate took 40+ seconds to return me the data! I also noticed the more data I have the more time it will take.

Below are my entities

Stock

    public class Stock implements java.io.Serializable {
    
        private Integer idstock;
        @JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
        private Product product;
        private int quantity;
        private Date dateCreated;
        private Date lastUpdated;
    
        public Stock() {
        }
    
        public Stock(Product product, int quantity) {
            this.product = product;
            this.quantity = quantity;
        }
    
        public Stock(Product product, int quantity, Date dateCreated, Date lastUpdated) {
            this.product = product;
            this.quantity = quantity;
            this.dateCreated = dateCreated;
            this.lastUpdated = lastUpdated;
        }
    
        public Integer getIdstock() {
            return this.idstock;
        }
    
        public void setIdstock(Integer idstock) {
            this.idstock = idstock;
        }
    
        public Product getProduct() {
            return this.product;
        }
    
        public void setProduct(Product product) {
            this.product = product;
        }
    
        public int getQuantity() {
            return this.quantity;
        }
    
        public void setQuantity(int quantity) {
            this.quantity = quantity;
        }
    
        public Date getDateCreated() {
            return this.dateCreated;
        }
    
        public void setDateCreated(Date dateCreated) {
            this.dateCreated = dateCreated;
        }
    
        public Date getLastUpdated() {
            return this.lastUpdated;
        }
    
        public void setLastUpdated(Date lastUpdated) {
            this.lastUpdated = lastUpdated;
        }
    
    }

VehicleModel

public class VehicleModel implements java.io.Serializable {

    private Integer idvehicleModel;
    private String modelName;
    private String code;
    private boolean enable;

    public VehicleModel() {
    }

    public VehicleModel(String modelName, boolean enable) {
        this.modelName = modelName;
        this.enable = enable;
    }

    public Integer getIdvehicleModel() {
        return this.idvehicleModel;
    }

    public void setIdvehicleModel(Integer idvehicleModel) {
        this.idvehicleModel = idvehicleModel;
    }

    public String getModelName() {
        return this.modelName;
    }

    public void setModelName(String modelName) {
        this.modelName = modelName;
    }

    public boolean getEnable() {
        return this.enable;
    }

    public void setEnable(boolean enable) {
        this.enable = enable;
    }

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

}

SparePart

public class SparePart implements java.io.Serializable {

    private Integer idsparePart;
    private String sparePartName;
    private String code;
    private boolean enable;

    public SparePart() {
    }

    public SparePart(String sparePartName, boolean enable) {
        this.sparePartName = sparePartName;
        this.enable = enable;
    }

    public Integer getIdsparePart() {
        return this.idsparePart;
    }

    public void setIdsparePart(Integer idsparePart) {
        this.idsparePart = idsparePart;
    }

    public String getSparePartName() {
        return this.sparePartName;
    }

    public void setSparePartName(String sparePartName) {
        this.sparePartName = sparePartName;
    }

    public boolean getEnable() {
        return this.enable;
    }

    public void setEnable(boolean enable) {
        this.enable = enable;
    }

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

}

This is just a part of the SQL output made by the Hibernate, you can see it tried to take data from each table by trying to execute SQL Queries individually.

Hibernate: select stock0_.idstock as idstock1_5_, stock0_.idproduct as idproduc2_5_, stock0_.quantity as quantity3_5_, stock0_.date_created as date_cre4_5_, stock0_.last_updated as last_upd5_5_ from aaa.stock stock0_
Hibernate: select product0_.idproduct as idproduc1_0_0_, product0_.idspare_part as idspare_2_0_0_, product0_.idvehicle_model as idvehicl3_0_0_, product0_.unit_price as unit_pri4_0_0_, product0_.qrcode as qrcode5_0_0_, product0_.enable as enable6_0_0_, product0_.minimum_stock_level as minimum_7_0_0_, product0_.stock_reorder_level as stock_re8_0_0_ from aaa.product product0_ where product0_.idproduct=?
Hibernate: select sparepart0_.idspare_part as idspare_1_4_0_, sparepart0_.spare_part_name as spare_pa2_4_0_, sparepart0_.code as code3_4_0_, sparepart0_.enable as enable4_4_0_ from aaa.spare_part sparepart0_ where sparepart0_.idspare_part=?
Hibernate: select vehiclemod0_.idvehicle_model as idvehicl1_10_0_, vehiclemod0_.model_name as model_na2_10_0_, vehiclemod0_.code as code3_10_0_, vehiclemod0_.enable as enable4_10_0_ from aaa.vehicle_model vehiclemod0_ where vehiclemod0_.idvehicle_model=?
Hibernate: select product0_.idproduct as idproduc1_0_0_, product0_.idspare_part as idspare_2_0_0_, product0_.idvehicle_model as idvehicl3_0_0_, product0_.unit_price as unit_pri4_0_0_, product0_.qrcode as qrcode5_0_0_, product0_.enable as enable6_0_0_, product0_.minimum_stock_level as minimum_7_0_0_, product0_.stock_reorder_level as stock_re8_0_0_ from aaa.product product0_ where product0_.idproduct=?
Hibernate: select sparepart0_.idspare_part as idspare_1_4_0_, sparepart0_.spare_part_name as spare_pa2_4_0_, sparepart0_.code as code3_4_0_, sparepart0_.enable as enable4_4_0_ from aaa.spare_part sparepart0_ where sparepart0_.idspare_part=?
Hibernate: select vehiclemod0_.idvehicle_model as idvehicl1_10_0_, vehiclemod0_.model_name as model_na2_10_0_, vehiclemod0_.code as code3_10_0_, vehiclemod0_.enable as enable4_10_0_ from aaa.vehicle_model vehiclemod0_ where vehiclemod0_.idvehicle_model=?

If this is normal SQL this will use Joins. How can I fix this issue and get the data as fast as possible? More importantly any way to tell Hibernate to run the query as a single unit, and use joins or something?

Answer

Answer is to use joins.

select s from Stock s join fetch s.product p 
join fetch p.sparePart sp
join fetch p.vehicleModel v