Solucionado (ver solução)
Solucionado
(ver solução)
2
respostas

TypedQuery getResultList -> Fazendo n consultas para uma lista

Olá, estou tendo grande problema de performance com o getResult. Quando faço a query aparece o log do hibernate n consultas, para uma consulta com 100 itens demorou 26segundos.

Meu código:

        TypedQuery<ImageProduct> typedQuery = manager.createQuery("SELECT c FROM ImageProduct c " +
                "WHERE c.brand LIKE :searchedWord " +
                "OR c.idImage.name LIKE :searchedWord " +
                "OR c.category LIKE :searchedWord " +
                "OR c.subcategory LIKE :searchedWord"
                , ImageProduct.class)
                .setParameter("searchedWord", searchedWord)
                .setFirstResult(startAt)
                .setMaxResults(maxResults);

//        List<ImageProduct> results = typedQuery.getResultList();
```[

log do hibernate para 10 elementos:

Hibernate: 
    select
        imageprodu0_.IdImageProduct as IdImageP1_1_,
        imageprodu0_.Brand as Brand2_1_,
        imageprodu0_.Category as Category3_1_,
        imageprodu0_.Description as Descript4_1_,
        imageprodu0_.Enabled as Enabled5_1_,
        imageprodu0_.IdImage as IdImage8_1_,
        imageprodu0_.Industry as Industry6_1_,
        imageprodu0_.Subcategory as Subcateg7_1_ 
    from
        ImageProduct imageprodu0_ cross 
    join
        Image image1_ 
    where
        imageprodu0_.IdImage=image1_.IdImage 
        and (
            imageprodu0_.Brand like ? 
            or image1_.Name like ? 
            or imageprodu0_.Category like ? 
            or imageprodu0_.Subcategory like ?
        ) limit ?, ?
Hibernate: 
    select
        image0_.IdImage as IdImage1_0_0_,
        image0_.Enabled as Enabled2_0_0_,
        image0_.IdImageType as IdImageT5_0_0_,
        image0_.Image as Image3_0_0_,
        image0_.Name as Name4_0_0_,
        imagetype1_.IdImageType as IdImageT1_2_1_,
        imagetype1_.Enabled as Enabled2_2_1_,
        imagetype1_.Type as Type3_2_1_ 
    from
        Image image0_ 
    left outer join
        ImageType imagetype1_ 
            on image0_.IdImageType=imagetype1_.IdImageType 
    where
        image0_.IdImage=?
Hibernate: 
    select
        image0_.IdImage as IdImage1_0_0_,
        image0_.Enabled as Enabled2_0_0_,
        image0_.IdImageType as IdImageT5_0_0_,
        image0_.Image as Image3_0_0_,
        image0_.Name as Name4_0_0_,
        imagetype1_.IdImageType as IdImageT1_2_1_,
        imagetype1_.Enabled as Enabled2_2_1_,
        imagetype1_.Type as Type3_2_1_ 
    from
        Image image0_ 
    left outer join
        ImageType imagetype1_ 
            on image0_.IdImageType=imagetype1_.IdImageType 
    where
        image0_.IdImage=?
Hibernate: 
    select
        image0_.IdImage as IdImage1_0_0_,
        image0_.Enabled as Enabled2_0_0_,
        image0_.IdImageType as IdImageT5_0_0_,
        image0_.Image as Image3_0_0_,
        image0_.Name as Name4_0_0_,
        imagetype1_.IdImageType as IdImageT1_2_1_,
        imagetype1_.Enabled as Enabled2_2_1_,
        imagetype1_.Type as Type3_2_1_ 
    from
        Image image0_ 
    left outer join
        ImageType imagetype1_ 
            on image0_.IdImageType=imagetype1_.IdImageType 
    where
        image0_.IdImage=?
Hibernate: 
    select
        image0_.IdImage as IdImage1_0_0_,
        image0_.Enabled as Enabled2_0_0_,
        image0_.IdImageType as IdImageT5_0_0_,
        image0_.Image as Image3_0_0_,
        image0_.Name as Name4_0_0_,
        imagetype1_.IdImageType as IdImageT1_2_1_,
        imagetype1_.Enabled as Enabled2_2_1_,
        imagetype1_.Type as Type3_2_1_ 
    from
        Image image0_ 
    left outer join
        ImageType imagetype1_ 
            on image0_.IdImageType=imagetype1_.IdImageType 
    where
        image0_.IdImage=?
Hibernate: 
    select
        image0_.IdImage as IdImage1_0_0_,
        image0_.Enabled as Enabled2_0_0_,
        image0_.IdImageType as IdImageT5_0_0_,
        image0_.Image as Image3_0_0_,
        image0_.Name as Name4_0_0_,
        imagetype1_.IdImageType as IdImageT1_2_1_,
        imagetype1_.Enabled as Enabled2_2_1_,
        imagetype1_.Type as Type3_2_1_ 
    from
        Image image0_ 
    left outer join
        ImageType imagetype1_ 
            on image0_.IdImageType=imagetype1_.IdImageType 
    where
        image0_.IdImage=?
Hibernate: 
    select
        image0_.IdImage as IdImage1_0_0_,
        image0_.Enabled as Enabled2_0_0_,
        image0_.IdImageType as IdImageT5_0_0_,
        image0_.Image as Image3_0_0_,
        image0_.Name as Name4_0_0_,
        imagetype1_.IdImageType as IdImageT1_2_1_,
        imagetype1_.Enabled as Enabled2_2_1_,
        imagetype1_.Type as Type3_2_1_ 
    from
        Image image0_ 
    left outer join
        ImageType imagetype1_ 
            on image0_.IdImageType=imagetype1_.IdImageType 
    where
        image0_.IdImage=?
Hibernate: 
    select
        image0_.IdImage as IdImage1_0_0_,
        image0_.Enabled as Enabled2_0_0_,
        image0_.IdImageType as IdImageT5_0_0_,
        image0_.Image as Image3_0_0_,
        image0_.Name as Name4_0_0_,
        imagetype1_.IdImageType as IdImageT1_2_1_,
        imagetype1_.Enabled as Enabled2_2_1_,
        imagetype1_.Type as Type3_2_1_ 
    from
        Image image0_ 
    left outer join
        ImageType imagetype1_ 
            on image0_.IdImageType=imagetype1_.IdImageType 
    where
        image0_.IdImage=?
Hibernate: 
    select
        image0_.IdImage as IdImage1_0_0_,
        image0_.Enabled as Enabled2_0_0_,
        image0_.IdImageType as IdImageT5_0_0_,
        image0_.Image as Image3_0_0_,
        image0_.Name as Name4_0_0_,
        imagetype1_.IdImageType as IdImageT1_2_1_,
        imagetype1_.Enabled as Enabled2_2_1_,
        imagetype1_.Type as Type3_2_1_ 
    from
        Image image0_ 
    left outer join
        ImageType imagetype1_ 
            on image0_.IdImageType=imagetype1_.IdImageType 
    where
        image0_.IdImage=?
Hibernate: 
    select
        image0_.IdImage as IdImage1_0_0_,
        image0_.Enabled as Enabled2_0_0_,
        image0_.IdImageType as IdImageT5_0_0_,
        image0_.Image as Image3_0_0_,
        image0_.Name as Name4_0_0_,
        imagetype1_.IdImageType as IdImageT1_2_1_,
        imagetype1_.Enabled as Enabled2_2_1_,
        imagetype1_.Type as Type3_2_1_ 
    from
        Image image0_ 
    left outer join
        ImageType imagetype1_ 
            on image0_.IdImageType=imagetype1_.IdImageType 
    where
        image0_.IdImage=?
Hibernate: 
    select
        image0_.IdImage as IdImage1_0_0_,
        image0_.Enabled as Enabled2_0_0_,
        image0_.IdImageType as IdImageT5_0_0_,
        image0_.Image as Image3_0_0_,
        image0_.Name as Name4_0_0_,
        imagetype1_.IdImageType as IdImageT1_2_1_,
        imagetype1_.Enabled as Enabled2_2_1_,
        imagetype1_.Type as Type3_2_1_ 
    from
        Image image0_ 
    left outer join
        ImageType imagetype1_ 
            on image0_.IdImageType=imagetype1_.IdImageType 
    where
        image0_.IdImage=?
Hibernate: 
    select
        count(1) as col_0_0_ 
    from
        ImageProduct imageprodu0_
Hibernate: 
    select
        count(1) as col_0_0_ 
    from
        ImageProduct imageprodu0_ cross 
    join
        Image image1_ 
    where
        imageprodu0_.IdImage=image1_.IdImage 
        and (
            imageprodu0_.Brand like ? 
            or image1_.Name like ? 
            or imageprodu0_.Category like ? 
            or imageprodu0_.Subcategory like ?
        )
package br.com.test.model.hibernate;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToOne;
import javax.persistence.Table;

@Entity
@Table( name = "ImageProduct" )
public class ImageProduct {
    @Id
    @GeneratedValue( strategy = GenerationType.IDENTITY )
    @Column( name = "IdImageProduct" )
    private Integer idImageProduct;

    @OneToOne
    @JoinColumn( name = "IdImage", unique=true )
    private Image idImage;

    @Column( name = "Description" )
    private String description;

    @Column( name = "Industry" )
    private String industry;

    @Column( name = "Brand" )
    private String brand;

    @Column( name = "Category" )
    private String category;

    @Column( name = "Subcategory" )
    private String subcategory;

    @Column( name = "Enabled" )
    private Integer enabled;

    public Integer getIdImageProduct() {
        return idImageProduct;
    }

    public void setIdImageProduct(Integer idImageProduct) {
        this.idImageProduct = idImageProduct;
    }

    public Image getIdImage() {
        return idImage;
    }

    public void setIdImage(Image idImage) {
        this.idImage = idImage;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public String getIndustry() {
        return industry;
    }

    public void setIndustry(String industry) {
        this.industry = industry;
    }

    public String getBrand() {
        return brand;
    }

    public void setBrand(String brand) {
        this.brand = brand;
    }

    public String getCategory() {
        return category;
    }

    public void setCategory(String category) {
        this.category = category;
    }

    public String getSubcategory() {
        return subcategory;
    }

    public void setSubcategory(String subcategory) {
        this.subcategory = subcategory;
    }

    public Integer getEnabled() {
        return enabled;
    }

    public void setEnabled(Integer enabled) {
        this.enabled = enabled;
    }
}
2 respostas
solução!

Diego, está ocorrendo um problema de 1 + N pois o ORM está fazendo um cross join... tenta alterar a query para:

SELECT c FROM ImageProduct c join fetch c.idImage i
                WHERE (c.brand LIKE :searchedWord 
                OR i.name LIKE :searchedWord 
                OR c.category LIKE :searchedWord 
                OR c.subcategory LIKE :searchedWord)

Muito obrigado @guilherme romão, sua query solucionou meu problema.

Quer mergulhar em tecnologia e aprendizagem?

Receba a newsletter que o nosso CEO escreve pessoalmente, com insights do mercado de trabalho, ciência e desenvolvimento de software