Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import java.io.File;
- import java.io.FileNotFoundException;
- import java.io.IOException;
- import java.io.PrintWriter;
- import java.sql.Connection;
- import java.sql.DatabaseMetaData;
- import java.sql.DriverManager;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- import java.util.ArrayList;
- public class benchmark {
- static String inputFile = "C:/SQLite/FrenchDB/database_14_3.sqlite";
- static String outputFile = "C:/SQLite/CreatedDB/benchmarkDB.db";
- static int nbrRow = 1;
- static int nbrCol = 10;
- static int nbrIter = 5;
- public static void main(String[] args) throws SQLException, IOException {
- Connection connInput = DriverManager.getConnection("jdbc:sqlite:" + inputFile);
- Connection connOutput = DriverManager.getConnection("jdbc:sqlite:" + outputFile);
- createDb(connOutput, getStructureDb(connInput), nbrCol);
- String res = executeBenchmark(connInput, connOutput);
- csvWriter(res);
- showGraph();
- connInput.close();
- connOutput.close();
- }
- public static void showGraph() throws IOException {
- String[] command = {"C:\\Users\\ksiry\\AppData\\Local\\Continuum\\anaconda3\\envs\\work_env\\python.exe", "C:\\Users\\ksiry\\Documents\\Java Scripts\\benchmark_insert\\src\\graph.py"};
- ProcessBuilder processBuilder = new ProcessBuilder(command);
- Process process = processBuilder.start();
- }
- public static void csvWriter(String res) throws FileNotFoundException {
- PrintWriter writer = new PrintWriter(new File("C:/Users/ksiry/Desktop/test.csv"));
- StringBuilder builder = new StringBuilder();
- builder.append(res);
- writer.write(builder.toString());
- writer.close();
- System.out.println("Finish !");
- }
- public static String executeBenchmark(Connection connInput, Connection connOutput) throws SQLException {
- String finalRes = null;
- String head = "";
- String batchString = "Batch";
- String exeUpdString = "ExecuteUpdate";
- for (int i = 0; i < 6; i++) {
- long moyBatch = 0;
- long moyExUpd = 0;
- for (int j = 0; j < nbrIter; j++) {
- ArrayList<ResultSet> dataRows = selectRows(connInput, getStructureDb(connInput), nbrRow);
- long res = insertRowsBatch(dataRows, nbrRow, nbrCol, connOutput, getStructureDb(connInput));
- deleteRows(getStructureDb(connInput), connOutput);
- moyBatch = (j * moyBatch + res) / (j + 1);
- }
- for (int j = 0; j < nbrIter; j++) {
- ArrayList<ResultSet> dataRows = selectRows(connInput, getStructureDb(connInput), nbrRow);
- long res = insertRowsExUpd(dataRows, nbrRow, nbrCol, connOutput, getStructureDb(connInput));
- deleteRows(getStructureDb(connInput), connOutput);
- moyExUpd = (j * moyExUpd + res) / (j + 1);
- }
- head += "," + nbrRow;
- batchString += "," + (moyBatch / 1e6);
- exeUpdString += "," + (moyExUpd / 1e6);
- finalRes = head + "\n" + batchString + "\n" + exeUpdString;
- System.out.println("Boucle des " + nbrRow + " Terminée !");
- nbrRow *= 10;
- }
- return finalRes;
- }
- public static ArrayList<ResultSet> getStructureDb(Connection connInput) throws SQLException {
- DatabaseMetaData databaseMetaData = connInput.getMetaData();
- ResultSet tables = databaseMetaData.getTables(null, null, null, new String[] { "TABLE" });
- ResultSet columns = null;
- ArrayList<ResultSet> col = new ArrayList<ResultSet>();
- while (tables.next()) {
- columns = databaseMetaData.getColumns(null, null, tables.getString("TABLE_NAME"), null);
- col.add(columns);
- }
- return col;
- }
- public static String getPrimaryKey(ResultSet col) throws SQLException {
- Connection connInput = DriverManager.getConnection("jdbc:sqlite:" + inputFile);
- DatabaseMetaData databaseMetaData = connInput.getMetaData();
- String pk = "";
- ResultSet primaryKey = databaseMetaData.getPrimaryKeys(null, null, col.getString("TABLE_NAME"));
- if (primaryKey.next()) {
- pk = primaryKey.getString("COLUMN_NAME");
- }
- return pk;
- }
- public static ResultSet getForeignKey(ResultSet col) throws SQLException {
- Connection connInput = DriverManager.getConnection("jdbc:sqlite:" + inputFile);
- DatabaseMetaData databaseMetaData = connInput.getMetaData();
- ResultSet foreignKey = databaseMetaData.getImportedKeys(null, null, col.getString("TABLE_NAME"));
- return foreignKey;
- }
- public static void createDb(Connection connOutput, ArrayList<ResultSet> col, int nbrCol) throws SQLException {
- Statement statement = connOutput.createStatement();
- String query_createdb = null;
- for (int i = 0; i < col.size(); i++) {
- query_createdb = "CREATE TABLE IF NOT EXISTS " + col.get(i).getString("TABLE_NAME") + " (";
- String pk = getPrimaryKey(col.get(i));
- ResultSet foreignKey = getForeignKey(col.get(i));
- ArrayList<String> pk_table = new ArrayList<String>();
- ArrayList<String> pk_col = new ArrayList<String>();
- ArrayList<String> fk_col = new ArrayList<String>();
- ArrayList<String> cur_col = new ArrayList<String>();
- int curCol = 0;
- while (foreignKey.next()) {
- pk_table.add(foreignKey.getString("PKTABLE_NAME"));
- pk_col.add(foreignKey.getString("PKCOLUMN_NAME"));
- fk_col.add(foreignKey.getString("FKCOLUMN_NAME"));
- }
- while (col.get(i).next() && curCol < nbrCol) {
- String auto_incr = "";
- String prim_key = "";
- String references = "";
- String nullable = "";
- cur_col.add(col.get(i).getString("COLUMN_NAME"));
- if (col.get(i).getString("IS_AUTOINCREMENT").equals("YES")) {
- auto_incr = " ASC AUTOINCREMENT";
- }
- if (col.get(i).getString("IS_NULLABLE").equals("NO")) {
- nullable = " NOT NULL";
- }
- if (col.get(i).getString("COLUMN_NAME").equals(pk)) {
- prim_key = " PRIMARY KEY";
- }
- for (int j = 0; j < fk_col.size(); j++) {
- if (col.get(i).getString("COLUMN_NAME").equals(fk_col.get(j))) {
- references = " REFERENCES " + pk_table.get(j) + " (" + pk_col.get(j) + ")";
- }
- }
- query_createdb += "[" + col.get(i).getString("COLUMN_NAME") + "]" + " "
- + col.get(i).getString("TYPE_NAME") + prim_key + auto_incr + references + nullable + ", ";
- curCol++;
- }
- query_createdb = query_createdb.substring(0, query_createdb.length() - 2);
- query_createdb += ");";
- statement.executeUpdate(query_createdb);
- foreignKey.close();
- }
- statement.close();
- }
- public static ArrayList<ResultSet> selectRows(Connection connInput, ArrayList<ResultSet> col, int nbrRow) throws SQLException {
- Statement statement = connInput.createStatement();
- ResultSet data = null;
- ArrayList<ResultSet> dataRows = new ArrayList<ResultSet>();
- for (int i = 0; i < col.size(); i++) {
- String nRow = " LIMIT " + (nbrRow * 10);
- String query_selectrows = "SELECT * FROM " + col.get(i).getString("TABLE_NAME") + nRow;
- data = statement.executeQuery(query_selectrows);
- dataRows.add(data);
- }
- return dataRows;
- }
- public static long insertRowsBatch(ArrayList<ResultSet> dataRows, int nbrRow, int nbrCol, Connection connOutput, ArrayList<ResultSet> col) throws SQLException {
- ArrayList<String> cur_col = new ArrayList<String>();
- long time = 0;
- String prefix = "";
- for (int i = 0; i < col.size(); i++) {
- StringBuilder query_insertrows = new StringBuilder();
- query_insertrows.append("INSERT INTO " + col.get(i).getString("TABLE_NAME") + " (");
- while (col.get(i).next()) {
- cur_col.add(col.get(i).getString("COLUMN_NAME"));
- }
- prefix = "";
- for (int j = 0; j < cur_col.size() && j < nbrCol; j++) {
- query_insertrows.append(prefix);
- prefix = ",";
- query_insertrows.append("[" + cur_col.get(j) + "]");
- }
- query_insertrows.append(") VALUES (");
- prefix = "";
- for (int j = 0; j < cur_col.size() && j < nbrCol; j++) {
- query_insertrows.append(prefix);
- prefix = ",";
- query_insertrows.append("?");
- }
- query_insertrows.append(")");
- PreparedStatement preparedStatement = connOutput.prepareStatement(query_insertrows.toString());
- int count = 0;
- while (dataRows.get(i).next()) {
- long startTime = 0;
- long endTime = 0;
- for (int j = 0; j < cur_col.size() && j < nbrCol; j++) {
- String str = dataRows.get(i).getString(cur_col.get(j));
- if (str != null) {
- preparedStatement.setString(j + 1, str);
- } else {
- preparedStatement.setString(j + 1, null);
- }
- }
- startTime = System.nanoTime();
- preparedStatement.addBatch();
- endTime = System.nanoTime();
- time += (endTime - startTime);
- startTime = 0;
- endTime = 0;
- if (count > nbrRow) {
- connOutput.setAutoCommit(false);
- startTime = System.nanoTime();
- preparedStatement.executeBatch();
- endTime = System.nanoTime();
- connOutput.setAutoCommit(true);
- count = 0;
- } else {
- count++;
- }
- time += (endTime - startTime);
- }
- connOutput.setAutoCommit(false);
- preparedStatement.executeBatch();
- connOutput.setAutoCommit(true);
- }
- return time;
- }
- public static long insertRowsExUpd(ArrayList<ResultSet> dataRows, int nbrRow, int nbrCol, Connection connOutput, ArrayList<ResultSet> col) throws SQLException {
- ArrayList<String> cur_col = new ArrayList<String>();
- long time = 0;
- Statement statement = connOutput.createStatement();
- String prefix = "";
- for (int i = 0; i < col.size(); i++) {
- StringBuilder query_insertrows = new StringBuilder();
- query_insertrows.append("INSERT INTO " + col.get(i).getString("TABLE_NAME") + " (");
- while (col.get(i).next()) {
- cur_col.add(col.get(i).getString("COLUMN_NAME"));
- }
- prefix = "";
- for (int j = 0; j < cur_col.size() && j < nbrCol; j++) {
- query_insertrows.append(prefix);
- prefix = ",";
- query_insertrows.append("[" + cur_col.get(j) + "]");
- }
- query_insertrows.append(") VALUES (");
- StringBuilder query_insert = query_insertrows;
- int count = 0;
- while (dataRows.get(i).next()) {
- long startTime = 0;
- long endTime = 0;
- prefix = "";
- for (int j = 0; j < cur_col.size() && j < nbrCol; j++) {
- String str = dataRows.get(i).getString(cur_col.get(j));
- query_insertrows.append(prefix);
- prefix = ",";
- if(str != null)
- {
- query_insert.append("'" + str.replace("'", "''") + "'");
- }
- else
- {
- query_insert.append("''");
- }
- }
- query_insert.append(")");
- if(count > nbrRow)
- {
- startTime = System.nanoTime();
- statement.executeUpdate(query_insert.toString());
- endTime = System.nanoTime();
- time += (endTime - startTime);
- query_insert = query_insertrows;
- count = 0;
- query_insert.append(",(");
- }
- else
- {
- query_insert.append(",(");
- }
- count++;
- }
- }
- return time;
- }
- public static void deleteRows(ArrayList<ResultSet> col, Connection connOutput) throws SQLException {
- Statement statement = connOutput.createStatement();
- for (int i = 0; i < col.size(); i++) {
- String query_deleterows = "DELETE FROM " + col.get(i).getString("TABLE_NAME");
- statement.executeUpdate(query_deleterows);
- }
- statement.close();
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement