¿Como insertar mas de 100 mil registros en una tabla?

publicado por: Anonymous

Tengo un ResultSet el cual trae mas de 100 mil datos en total para ser insertados, el problema es que mi método inserta valor por valor en el query ya que hay campos que van vacíos, el método esta de la siguiente manera, solo es una porción:

try
        {
            rs.beforeFirst();
            stmt = cnn.createStatement();
            while(rs.next())
            {
               documentID = rs.getInt(1);
               folio = rs.getString(5);
               ficha = rs.getString(6);
               nombre = rs.getString(7);
               tipoArchivo = "PDF";
               created = rs.getString(9);
               created = created.substring(0, 19);
               createdBy = rs.getString(10);
               modified = rs.getString(11);
               modified = modified.substring(0, 19);
               modifiedBy = rs.getString(12);
               String Qry = "INSERT INTO wfattxdoc(document_id, folio, ficha, nombre, document_created_date, document_createdby, document_modified_date, document_modifiedby)" +
                       " VALUES (" + documentID + ", " + "'" + folio + "'" + ", " + "'" + ficha + "'" + ", " + "'" + nombre + "'" + ", " +
                       "'" + created + "'" + ", " + "'" + createdBy + "'" + ", " +
                       "'" + modified + "'" + ", " + "'" + modifiedBy + "'" + ");";
               stmt.executeUpdate(Qry);
            }
            stmt.close();
        }

Esto causa que vaya muy lento, y se tarde hasta mas de 6 horas (no miento) en realizar todo el proceso. Mi duda es ¿Existe otra manera mas eficaz de insertar miles de registros de una forma mas rápida? No me sale ningún error, el único problema es la lentitud.

solución

Deberías usar un PreparedStatement y ejecutar las sentencias en lotes. Para esto, existen los métodos addBatch y executeBatch (heredado de Statement). Esto permite enviar un lote de sentencias a la base de datos en lugar de mandar las sentencias de 1 en 1. Esta forma de trabajo aplica a cualquier motor de base de datos con driver JDBC compatible, no solo a MySQL.

Tu código luciría así:

//esto decláralo fuera del método
private static final int REGISTROS_BATCH = 1000;

//dentro de tu método
String sql = "INSERT INTO wfattxdoc(document_id, folio, ficha, nombre, document_created_date, document_createdby, document_modified_date, document_modifiedby) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
try {
    rs.beforeFirst();
    try (PreparedStatement pstmt = cnn.prepareStatement(sql)) {
        int counter = 0;
        while(rs.next()) {
            documentID = rs.getInt(1);
            folio = rs.getString(5);
            ficha = rs.getString(6);
            nombre = rs.getString(7);
            tipoArchivo = "PDF";
            created = rs.getString(9);
            created = created.substring(0, 19);
            createdBy = rs.getString(10);
            modified = rs.getString(11);
            modified = modified.substring(0, 19);
            modifiedBy = rs.getString(12);

            pstmt.setInt(1, document_id);
            pstmt.setString(2, folio);
            pstmt.setString(3, ficha);
            pstmt.setString(4, nombre);
            //sería mejor usar java.sql.Timestamp en lugar de String
            pstmt.setString(5, created);
            pstmt.setString(6, createdBy);
            pstmt.setString(7, modified);
            pstmt.setString(8, modifiedBy);
            //agregamos la sentencia al lote
            pstmt.addBatch();
            //aumentamos el contados de lote
            counter++;
            //al tener 1000 o más sentencias, mandamos todas a ejecutar
            //y reiniciamos el contador
            if (counter == REGISTROS_BATCH) {
                pstmt.executeBatch();
                counter = 0;
            }
        }
        //revisamos si todavía hay sentencias pendientes de ejecutar
        if (counter > 0) {
            pstmt.executeBatch();
        }
    }
} (catch SQLException e) {
    //maneja tus excepciones...
}

En MySQL, para mejorar aún más el rendimiento de estas sentencias, puedes agregar los parámetros useServerPrepStmts y rewriteBatchedStatements al momento de abrir la conexión1. Por ejemplo, tu cadena de conexión puede lucir así:

jdbc:mysql://<servidor>:<puerto>/<nombre bd>?useServerPrepStmts=false&rewriteBatchedStatements=true

1 Adaptado de JDBC batch insert performance

Respondido por: user227

Leave a Reply

Your email address will not be published. Required fields are marked *