Fastest way to write a list of object extracted from two databases to an txt file in Java

Question

I have a function that searches for a list and for each item in the list I have another list. These two lists will have to be transformed into a text file to be imported into a software. The problem I'm having is slow ...

My first list has about 500 records, and every record in that list has another list that can range from 1 record to infinity. This takes about 20 minutes to complete. When I comment the updates (updates the status of each item of the 2 list) the time drops to 9 minutes.

Can someone help me ?

private String getExportacaoApontamento(
            String idsExportar,
            String dataInicial,
            String dataFinal,
            String status,
            String tipoFiltro,
            String filtro,
            String turma,
            boolean exportarTodos) throws SQLException {
        StringBuilder stringBuilder = new StringBuilder();        

        try (
                Connection conMySql = U_Conexao.getConexaoMySQL();
                Connection conOracle = U_Conexao.getConexaoOracle()) {

            if (conMySql != null) {
                List<C_Sequencia> listSequencia;
                R_Sequencia r_Sequencia = new R_Sequencia(conMySql, null);

                if (status.equals(String.valueOf(C_Status.TODOS))) {
                    status = C_Status.PENDENTE + ", " + C_Status.EXPORTADO;
                }

                String orderBy = "S." + C_Sequencia.DATA + ", S." + C_Sequencia.COD_COLETOR + ", S." + C_Sequencia.COD_FISCAL;

                if (exportarTodos == false) {
                    listSequencia = r_Sequencia.listExportarId(idsExportar, dataInicial, dataFinal, orderBy);
                } else {
                    tipoFiltro = verificarFiltroApontamento(tipoFiltro);

                    if (filtro == null || filtro.isEmpty()) {
                        listSequencia = r_Sequencia.listExportarData(dataInicial, dataFinal, status, turma, orderBy);
                    } else {
                        listSequencia = r_Sequencia.listExportarFiltro(dataInicial, dataFinal, tipoFiltro, filtro, status, turma, orderBy);
                    }
                }

                if (!listSequencia.isEmpty()) {

                    if (!verificarLiberacoes(listSequencia, conMySql, conOracle)) {
                        return "-1";
                    }

                    C_Sequencia seqAntiga = null;
                    R_Producao r_Producao = new R_Producao(conMySql, conOracle);

                    for (C_Sequencia sequencia : listSequencia) {

                        C_Sequencia seqNova = sequencia;

                        String retornoSequencia = gerarSequencia(seqAntiga, seqNova);

                        if (!retornoSequencia.isEmpty()) {
                            stringBuilder.append(retornoSequencia);                             
                        }

                        List<C_Producao> listProducao = r_Producao.getProducaoExportar(sequencia.getChave(), status);
                        for (C_Producao producao : listProducao) {
                            DecimalFormat decimal = new DecimalFormat("########.00");
                            String prod = String.valueOf(decimal.format(Double.parseDouble(producao.getProducao())));
                            String meta = String.valueOf(decimal.format(Double.parseDouble(producao.getMeta())));
                            prod = prod.replace(",", "");
                            meta = meta.replace(",", "");
                            stringBuilder.append("02;")
                                    .append(String.format("%5d", producao.getCodColetor())).append(";")
                                    .append(U_DataHora.formatarData(producao.getDataHora(), U_DataHora.DDMMYYYY_HHMMSS, U_DataHora.DDMMYYYY)).append(";")
                                    .append(String.format("%10d", producao.getFuncionario().getMatricula())).append(";")
                                    .append(String.format("%9d", sequencia.getCodCc())).append(";")
                                    .append(String.format("%4d", sequencia.getCodOp())).append(";")
                                    .append(String.format("%4d", sequencia.getCodOp())).append(";")
                                    .append("      ;")
                                    .append(String.format("%6d", Long.parseLong(sequencia.getCodFazenda()))).append(";")
                                    .append(String.format("%6d", Long.parseLong(sequencia.getCodTalhao()))).append(";")
                                    .append(String.format("%10s", prod)).append(";")
                                    .append("        0000000;")
                                    .append(";")
                                    .append("          ;")
                                    .append(String.format("%9s", meta)).append(";")
                                    .append(String.format("%4d", sequencia.getCodSequencia())).append(";")
                                    .append(" ;")
                                    .append(" ;")
                                    .append("     ;")
                                    .append("     ;")
                                    .append(" ;")
                                    .append(" ;")
                                    .append(String.format("%9d", sequencia.getCodOs())).append(";")
                                    .append("\r\n");

                            if (producao.getStatus().getStatus() != C_Status.EXPORTADO) {
                                r_Producao.atualizarStatus(producao.getChave(), C_Status.EXPORTADO); // Atualiza status para exportado
                            }
                        }

                        // Atualiza o status de todas as produções da sequencia da posição atual
                        //r_Producao.atualizaStatusProducoesPorChaveSequencia(sequencia.getChave(), C_Status.EXPORTADO); 

                        seqAntiga = seqNova;

                        if (sequencia.getStatus().getStatus() != C_Status.EXPORTADO) {
                            r_Sequencia.atualizarStatus(sequencia.getChave(), C_Status.EXPORTADO); // Atualiza status para exportado
                        }
                    }
                }
            }
        } catch (Exception e) {
            U_Log.erro(TAG, e.toString());
            return e.toString();
        }
        return stringBuilder.toString();
    }

private String gerarSequencia(C_Sequencia seqAntiga, C_Sequencia seqNova) {
        StringBuilder sequenciaBuilder = new StringBuilder();
        String texto = sequenciaBuilder.append("01;")
                .append(String.format("%5d", seqNova.getCodColetor())).append(";")
                .append(U_DataHora.formatarData(seqNova.getData(), U_DataHora.DDMMYYYY_HHMMSS, U_DataHora.DDMMYYYY)).append(";")
                .append(String.format("%10d", seqNova.getCodFiscal())).append(";")
                .append(String.format("%10d", seqNova.getCodFiscal())).append(";")
                .append("\r\n").toString();

        if (seqAntiga != null) {
            if (!seqAntiga.getData().equals(seqNova.getData())
                    || !Objects.equals(seqAntiga.getCodColetor(), seqNova.getCodColetor())
                    || !Objects.equals(seqAntiga.getCodFiscal(), seqNova.getCodFiscal())) {

                return texto;

            } else {
                return "";
            }
        } else {
            return texto;
        }
    }

Result here

Here i commented update in the DB

for (C_Sequencia sequencia : listSequencia) {
  ...
  for (C_Producao producao : listProducao) {
    ... 
    // update status in DB
    if (producao.getStatus().getStatus() != C_Status.EXPORTADO) {
      r_Producao.atualizarStatus(producao.getChave(), C_Status.EXPORTADO);
    }
  }
 // update status in DB
 if (sequencia.getStatus().getStatus() != C_Status.EXPORTADO) {
   r_Sequencia.atualizarStatus(sequencia.getChave(), C_Status.EXPORTADO); 
 }
}

Result: Total time 9 minutes

Now, i commented the part that I built string

for (C_Sequencia sequencia : listSequencia) {
      ...
      // here build a part of the string in method " gerarSequencia "
      // i commented a part inside method " gerarSequencia "
      String retornoSequencia = gerarSequencia(seqAntiga, seqNova);
      ...

      for (C_Producao producao : listProducao) {
        ... 
        // Here i commented another part of the string
         ...
      }
 }

Result: Total time is 14 minutes - Practically running only the updates lines

A) read about java naming conventions "_" has no place in class names. B) this site is primarily English. So posting a lot of non-English source code *dramatically* decreases your chances of getting good answers C) read about **clean code** . Your poor method is doing way too many things. You should put each "functionality" in its own method (or class), with clear, reasonable interfaces between them. — GhostCat, Dec 06 '18 at 11:54
In other words: just the way you present your input here makes me think "I dont want to spend my time reading it". Others obviously don't mind, yet: the more people *want* to read your code, the better for you. — GhostCat, Dec 06 '18 at 11:55
What is the elapsed time if you comment out string building code ? — Shamit Verma, Dec 06 '18 at 13:06
Can you create a reproducible test-case ? (I.e. maybe post data + code after removing / masking sensitive data) — Shamit Verma, Dec 06 '18 at 14:52
@Shamit Verma, i update my question. Was that what you were talking about? — Edi, Dec 06 '18 at 15:45

score 2 · Accepted Answer · answered Dec 12 '18 at 09:45

2

i solved my problem. I've changed logic. The actual problem was doing many SELECT in the database and this was making it extremely slow. Now is a single SELECT. The total time is now 6 seconds.

answered Dec 12 '18 at 09:45

Edi

615
5
15

score 1 · Answer 2 · answered Dec 06 '18 at 11:52

1

You have 2 problems:

1 - you are actually not doing the write to the file, but returning some big String. I don't think processing such large lists in memory is good idea.

2 - How are you doing the update? I guess the problem is that you are hitting db every row. You should think how to do it at once.

answered Dec 06 '18 at 11:52

Wojtek Mlodzianowski

398
2
17

I updated with the result of the query. After retrieving the giant text, I write. At each turn, I need to update the status of each item ... because I can not update later – Edi Dec 06 '18 at 12:05

Hans Schreuder · Answer 3 · 2018-12-06T11:51:25.160

private File writeRecords(String filenaam, List<String> records) throws IOException
{
    File file = new File(filenaam);
    try (FileOutputStream fos = new FileOutputStream(file); 
         PrintWriter writer = new PrintWriter(fos))
    {
        records.forEach(rec -> writer.println(rec));
        writer.flush();
    }
    return file;
}

And if you want to compress the file:

private void compressFiles(String zipfile, List<File> files) throws IOException
{
    try (FileOutputStream fos = new FileOutputStream(zipfile);
            ZipOutputStream zos = new ZipOutputStream(new BufferedOutputStream(fos)))
    {
        for (File file : files)
        {
            ZipEntry entry = new ZipEntry(file.getName());
            byte[] bytes = Files.readAllBytes(Paths.get(file.getAbsolutePath()));
            zos.putNextEntry(entry);
            zos.write(bytes, 0, bytes.length);
            zos.closeEntry();
        }
        zos.finish();
        zos.flush();
    }
}

The problem é my function ? Could you explain? – Edi Dec 06 '18 at 11:50 — Edi, Dec 06 '18 at 11:50

score 0 · Answer 4 · answered Dec 06 '18 at 11:53

0

This link may helpful for your performance issue, this may be not file writing problem but as far as I believe the issue is String manipulation problem. What's the fastest way to concatenate two Strings in Java?

answered Dec 06 '18 at 11:53

Java-Dev

438
4
20

I did some tests, but the time was the same – Edi Dec 06 '18 at 14:47

Fastest way to write a list of object extracted from two databases to an txt file in Java

4 Answers4