How to increase performance in EclipseLink when dealing with BLOB fields?

Question

My Java application has become very slow due to the BLOB field that each entity has. This field is usually used to store a PDF file and whenever i have to list all the objects, it takes a considerable amount of time until the persistence provider can finish its job. I looked for answers on how to deal with such type of data, but some of them talk about storing the BLOB in a separate table and then using FetchType.LAZY. Is there any way to fetch this field only when needed without having to create another table? If not, is creating another table the most appropriate solution?

Entity Code

@Cache(alwaysRefresh = true)
public class ScdDocumento implements Serializable, MultipleSelector {
    @Transient
    public static final String QUERY_RELATORIO_DOC_LOC = "consultas/ctrl_docs/consulta_relatorio_doc_local.txt";

    @Transient
    public static final String QUERY_RELATORIO_DOC_GRUPO = "consultas/ctrl_docs/consulta_relatorio_doc_grupo.txt";

    @Id
    @Column(name = "nome", length = 50)
    private String nome;

    @Column(name = "revisao")
    private int revisao;

    @Column(name = "id_tipo")
    private int id_tipo;

    @Column(name = "situacao", length = 1)
    private String situacao;

    @Column(name = "doc_blob_nome", length = 50)
    private String doc_blob_nome;

    @Lob 
    @Basic(fetch = FetchType.LAZY)
    @Column(name = "documento_blob", nullable = false)
    private byte[] documento_blob; //The field that impacts the application perfomance

    @Column(name = "abrangencia_geral")
    private int abrangencia_geral;

    @ManyToMany
    @JoinTable(name = "SCD_DOC_GRUPO", joinColumns = {@JoinColumn(name = "id_doc")},
        inverseJoinColumns = {@JoinColumn(name = "id_grupo")})
    private Set<SosGrupo> grupos;

    @ManyToOne
    @JoinColumn(name = "id_tipo", insertable = false, updatable = false)
    private ScdTipo tipo;

    @ManyToMany
    @JoinTable(name = "SCD_REFERENCIA", joinColumns = {@JoinColumn(name = "doc_pai")},
        inverseJoinColumns = {@JoinColumn(name = "doc_filho")})
    private Set<ScdDocumento> referencias;

    @ManyToMany
    @JoinTable(name = "SCD_REFERENCIA", joinColumns = {@JoinColumn(name = "doc_filho")},
        inverseJoinColumns = {@JoinColumn(name = "doc_pai")})
    private Set<ScdDocumento> referenciadoPor;

    @ManyToMany
    @JoinTable(name = "SCD_PALAVRA_REFERENCIA", joinColumns = {@JoinColumn(name = "documento")},
        inverseJoinColumns = {@JoinColumn(name = "palavra")})
    private Set<ScdPalavraChave> palavrasChaves;

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "documento")
    private Set<ScdOrdem> ordens;

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "documento")
    private Set<ScdArquivoOs> arquivosOs;

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "documento")
    private Set<ScdArquivoHistorico> arquivosHistorico;

    @ManyToMany(cascade = {CascadeType.REFRESH, CascadeType.MERGE})
    @JoinTable(name = "SCD_LOCAL_DOC", joinColumns = {@JoinColumn(name = "id_doc")},
        inverseJoinColumns = {@JoinColumn(name = "id_local")})
    private Set<ScdLocal> locais;

    @Override
    public String getNome() {
        return nome;
    }

    public void setNome(String nome) {
        this.nome = nome;
    }

    public int getRevisao() {
        return revisao;
    }

    public void setRevisao(int revisao) {
        this.revisao = revisao;
    }

    public int getIdTipo() {
        return id_tipo;
    }

    public void setIdTipo(int id_tipo) {
        this.id_tipo = id_tipo;
    }

    public String getSituacao() {
        return situacao;
    }

    public void setSituacao(String situacao) {
        this.situacao = situacao;
    }

    public String getDocBlobNome() {
        return doc_blob_nome;
    }

    public void setDocBlobNome(String doc_blob_nome) {
        this.doc_blob_nome = doc_blob_nome;
    }

    public byte[] getDocumentoBlob() {
        return documento_blob;
    }

    public void setDocumentoBlob(byte[] documento_blob) {
        this.documento_blob = documento_blob;
    }

    public int getAbrangenciaGeral() {
        return abrangencia_geral;
    }

    public void setAbrangenciaGeral(int abrangencia_geral) {
        this.abrangencia_geral = abrangencia_geral;
    }

    public Set<SosGrupo> getGrupos() {
        return grupos;
    }

    public void setGrupos(Set<SosGrupo> grupo) {
        this.grupos = grupo;
    }

    public ScdTipo getTipo() {
        return tipo;
    }

    public void setTipo(ScdTipo tipo) {
        this.tipo = tipo;
    }

    public Set<ScdDocumento> getReferencias() {
        return referencias;
    }

    public void setReferencias(Set<ScdDocumento> referencias) {
        this.referencias = referencias;
    }

    public Set<ScdDocumento> getReferenciadoPor() {
        return referenciadoPor;
    }

    public void setReferenciadoPor(Set<ScdDocumento> referenciadoPor) {
        this.referenciadoPor = referenciadoPor;
    }

    public Set<ScdPalavraChave> getPalavrasChaves() {
        return palavrasChaves;
    }

    public void setPalavrasChaves(Set<ScdPalavraChave> palavrasChaves) {
        this.palavrasChaves = palavrasChaves;
    }

    public Set<ScdOrdem> getOrdens() {
        return ordens;
    }

    public void setOrdens(Set<ScdOrdem> ordens) {
        this.ordens = ordens;
    }

    public Set<ScdArquivoOs> getArquivosOs() {
        return arquivosOs;
    }

    public void setArquivosOs(Set<ScdArquivoOs> arquivosOs) {
        this.arquivosOs = arquivosOs;
    }

    public Set<ScdArquivoHistorico> getArquivosHistorico() {
        return arquivosHistorico;
    }

    public void setArquivosHistorico(Set<ScdArquivoHistorico> arquivosHistorico) {
        this.arquivosHistorico = arquivosHistorico;
    }

    public Set<ScdLocal> getLocais() {
        return locais;
    }

    public void setLocais(Set<ScdLocal> locais) {
        this.locais = locais;
    }    

    @Override
    public String getIdRef() {
        return nome;
    }

    @Override
    public String getDesc() {
        return tipo.getNome();
    }
}

Methods that are causing the problem

GUI

private void loadDocumentTable(String situacao) {
    mapaDocumentos = new TreeMap<>();
    modelDocumentos.setRowCount(0);

    docdao.getCriteria("situacao", situacao).forEach((e) -> {
        mapaDocumentos.put(e.getNome(), e);
    });

    mapaDocumentos.entrySet().forEach((e) -> {
        String desc = e.getValue().getDocBlobNome();
        modelDocumentos.addRow(new Object[]{e.getKey(), desc.substring(0, desc.length() - 3), e.getValue().getRevisao()});
    });
}

Generic Dao

@Override
public List<T> getCriteria(String column, Object value){
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<T> cq = cb.createQuery(clazz);
    Root<T> root = cq.from(clazz);
    EntityType<T> ent = root.getModel();
    cq.where(cb.equal(root.get(ent.getSingularAttribute(column)), value.toString()));

    return em.createQuery(cq).getResultList();
}

Table Model

Persistence

Added this to my persistence.xml, but eclipselink still fetched the byte[] field eagerly.

Maven Plugin

         <plugin>
            <groupId>de.empulse.eclipselink</groupId>
            <artifactId>staticweave-maven-plugin</artifactId>
            <version>1.0.0</version>
            <executions>
                <execution>
                    <phase>process-classes</phase>
                    <goals>
                        <goal>weave</goal>
                    </goals>
                    <configuration>
                        <persistenceXMLLocation>META-INF/persistence.xml</persistenceXMLLocation>
                        <logLevel>FINE</logLevel>
                    </configuration>
                </execution>
            </executions>
            <dependencies>
               <dependency>
                   <groupId>org.eclipse.persistence</groupId>
                   <artifactId>org.eclipse.persistence.jpa</artifactId>
                   <version>2.5.2</version>
               </dependency>
            </dependencies>
        </plugin>

Final Edit

The staticweave-maven-plugin actually worked, but I need to build the project everytime I change something in order for the performance to be boosted. This happens because the weaving is static, so it's applied at build time and not when running the project using the IDE.

Have you tried marking it as `@Lob` and also using `@Basic(fetch=LAZY)` ? It is a hint to a JPA provider, so no idea if yours actually respects that — , Jun 04 '18 at 13:57
I added these 2 annotations, but the performance was the same. If i remove the field the load times are almost instantaneous. — Jean Willian S. J., Jun 04 '18 at 14:32
You say the performance was the same but make no comment on the SQL issued. Maybe your JPA provider just ignored the LAZY setting, but then you won't know if you don't look at the JPA providers log — , Jun 04 '18 at 18:02
I think it ignored the notation, because I teste as Andrei noted in this question https://stackoverflow.com/questions/18693849/jpa-fetchtype-lazy-is-not-working by searching for an object and then retrieving the marked field after the object had been detached. I was still able to get the data because it had already been fetched. Also tried to: add to my persistence.xml and use the staticweave-maven-plugin — Jean Willian S. J., Jun 05 '18 at 12:55
I added more information about the methods used and other details. — Jean Willian S. J., Jun 05 '18 at 13:18
EclipseLink allows fetching after an entity is detached as long as the context is still available. If you want to see if weaving 'worked', turn on logging and see what SQL is generated, and when. — Chris, Jun 06 '18 at 13:54

score 1 · Accepted Answer · answered Jun 04 '18 at 14:35

JPA basics also allow specifying a fetch type of LAZY, which would prevent loading the BLOB until you access it in the entity. OneToOne, ManyToOne and basic mappings require byte code enhancement of your entities for EclipseLink to gain notification when you access a lazy attribute and load it, which is described here as weaving. This will ensure that it isn't loaded by default.

With the use of weaving, you can also use entity graphs to specify what is loaded and when. This can allow loading the blob in a single query with the rest of the entity when it is to be used, and exclude it by default elsewhere. See What is the diffenece between FETCH and LOAD for Entity graph of JPA? for info on load and fetch graphs.

How to increase performance in EclipseLink when dealing with BLOB fields?

1 Answers1