· 6 years ago · Apr 02, 2019, 09:52 AM
1import java.io.File;
2import java.io.FileNotFoundException;
3import java.io.IOException;
4import java.io.PrintWriter;
5import java.sql.Connection;
6import java.sql.DatabaseMetaData;
7import java.sql.DriverManager;
8import java.sql.PreparedStatement;
9import java.sql.ResultSet;
10import java.sql.SQLException;
11import java.sql.Statement;
12import java.util.ArrayList;
13
14public class benchmark {
15 static String inputFile = "C:/SQLite/FrenchDB/database_14_3.sqlite";
16 static String outputFile = "C:/SQLite/CreatedDB/benchmarkDB.db";
17 static int nbrRow = 1;
18 static int nbrCol = 10;
19 static int nbrIter = 5;
20
21 public static void main(String[] args) throws SQLException, IOException {
22 Connection connInput = DriverManager.getConnection("jdbc:sqlite:" + inputFile);
23 Connection connOutput = DriverManager.getConnection("jdbc:sqlite:" + outputFile);
24
25 createDb(connOutput, getStructureDb(connInput), nbrCol);
26 String res = executeBenchmark(connInput, connOutput);
27
28 csvWriter(res);
29 showGraph();
30
31 connInput.close();
32 connOutput.close();
33 }
34
35 public static void showGraph() throws IOException {
36 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"};
37 ProcessBuilder processBuilder = new ProcessBuilder(command);
38 Process process = processBuilder.start();
39 }
40
41 public static void csvWriter(String res) throws FileNotFoundException {
42 PrintWriter writer = new PrintWriter(new File("C:/Users/ksiry/Desktop/test.csv"));
43 StringBuilder builder = new StringBuilder();
44 builder.append(res);
45 writer.write(builder.toString());
46 writer.close();
47 System.out.println("Finish !");
48 }
49
50 public static String executeBenchmark(Connection connInput, Connection connOutput) throws SQLException {
51 String finalRes = null;
52 String head = "";
53 String batchString = "Batch";
54 String exeUpdString = "ExecuteUpdate";
55
56 for (int i = 0; i < 6; i++) {
57 long moyBatch = 0;
58 long moyExUpd = 0;
59 for (int j = 0; j < nbrIter; j++) {
60 ArrayList<ResultSet> dataRows = selectRows(connInput, getStructureDb(connInput), nbrRow);
61 long res = insertRowsBatch(dataRows, nbrRow, nbrCol, connOutput, getStructureDb(connInput));
62 deleteRows(getStructureDb(connInput), connOutput);
63 moyBatch = (j * moyBatch + res) / (j + 1);
64 }
65 for (int j = 0; j < nbrIter; j++) {
66 ArrayList<ResultSet> dataRows = selectRows(connInput, getStructureDb(connInput), nbrRow);
67 long res = insertRowsExUpd(dataRows, nbrRow, nbrCol, connOutput, getStructureDb(connInput));
68 deleteRows(getStructureDb(connInput), connOutput);
69 moyExUpd = (j * moyExUpd + res) / (j + 1);
70 }
71 head += "," + nbrRow;
72 batchString += "," + (moyBatch / 1e6);
73 exeUpdString += "," + (moyExUpd / 1e6);
74 finalRes = head + "\n" + batchString + "\n" + exeUpdString;
75 System.out.println("Boucle des " + nbrRow + " Terminée !");
76 nbrRow *= 10;
77 }
78
79 return finalRes;
80 }
81
82 public static ArrayList<ResultSet> getStructureDb(Connection connInput) throws SQLException {
83 DatabaseMetaData databaseMetaData = connInput.getMetaData();
84 ResultSet tables = databaseMetaData.getTables(null, null, null, new String[] { "TABLE" });
85 ResultSet columns = null;
86 ArrayList<ResultSet> col = new ArrayList<ResultSet>();
87
88 while (tables.next()) {
89 columns = databaseMetaData.getColumns(null, null, tables.getString("TABLE_NAME"), null);
90 col.add(columns);
91 }
92
93 return col;
94 }
95
96 public static String getPrimaryKey(ResultSet col) throws SQLException {
97 Connection connInput = DriverManager.getConnection("jdbc:sqlite:" + inputFile);
98 DatabaseMetaData databaseMetaData = connInput.getMetaData();
99 String pk = "";
100
101 ResultSet primaryKey = databaseMetaData.getPrimaryKeys(null, null, col.getString("TABLE_NAME"));
102 if (primaryKey.next()) {
103 pk = primaryKey.getString("COLUMN_NAME");
104 }
105
106 return pk;
107 }
108
109 public static ResultSet getForeignKey(ResultSet col) throws SQLException {
110 Connection connInput = DriverManager.getConnection("jdbc:sqlite:" + inputFile);
111 DatabaseMetaData databaseMetaData = connInput.getMetaData();
112
113 ResultSet foreignKey = databaseMetaData.getImportedKeys(null, null, col.getString("TABLE_NAME"));
114
115 return foreignKey;
116 }
117
118 public static void createDb(Connection connOutput, ArrayList<ResultSet> col, int nbrCol) throws SQLException {
119 Statement statement = connOutput.createStatement();
120 String query_createdb = null;
121
122 for (int i = 0; i < col.size(); i++) {
123 query_createdb = "CREATE TABLE IF NOT EXISTS " + col.get(i).getString("TABLE_NAME") + " (";
124
125 String pk = getPrimaryKey(col.get(i));
126 ResultSet foreignKey = getForeignKey(col.get(i));
127
128 ArrayList<String> pk_table = new ArrayList<String>();
129 ArrayList<String> pk_col = new ArrayList<String>();
130 ArrayList<String> fk_col = new ArrayList<String>();
131
132 ArrayList<String> cur_col = new ArrayList<String>();
133
134 int curCol = 0;
135
136 while (foreignKey.next()) {
137 pk_table.add(foreignKey.getString("PKTABLE_NAME"));
138 pk_col.add(foreignKey.getString("PKCOLUMN_NAME"));
139 fk_col.add(foreignKey.getString("FKCOLUMN_NAME"));
140 }
141
142 while (col.get(i).next() && curCol < nbrCol) {
143 String auto_incr = "";
144 String prim_key = "";
145 String references = "";
146 String nullable = "";
147
148 cur_col.add(col.get(i).getString("COLUMN_NAME"));
149
150 if (col.get(i).getString("IS_AUTOINCREMENT").equals("YES")) {
151 auto_incr = " ASC AUTOINCREMENT";
152 }
153
154 if (col.get(i).getString("IS_NULLABLE").equals("NO")) {
155 nullable = " NOT NULL";
156 }
157
158 if (col.get(i).getString("COLUMN_NAME").equals(pk)) {
159 prim_key = " PRIMARY KEY";
160 }
161
162 for (int j = 0; j < fk_col.size(); j++) {
163 if (col.get(i).getString("COLUMN_NAME").equals(fk_col.get(j))) {
164 references = " REFERENCES " + pk_table.get(j) + " (" + pk_col.get(j) + ")";
165 }
166 }
167
168 query_createdb += "[" + col.get(i).getString("COLUMN_NAME") + "]" + " "
169 + col.get(i).getString("TYPE_NAME") + prim_key + auto_incr + references + nullable + ", ";
170 curCol++;
171 }
172 query_createdb = query_createdb.substring(0, query_createdb.length() - 2);
173 query_createdb += ");";
174
175 statement.executeUpdate(query_createdb);
176
177 foreignKey.close();
178 }
179
180 statement.close();
181 }
182
183 public static ArrayList<ResultSet> selectRows(Connection connInput, ArrayList<ResultSet> col, int nbrRow) throws SQLException {
184 Statement statement = connInput.createStatement();
185 ResultSet data = null;
186 ArrayList<ResultSet> dataRows = new ArrayList<ResultSet>();
187
188 for (int i = 0; i < col.size(); i++) {
189 String nRow = " LIMIT " + (nbrRow * 10);
190
191 String query_selectrows = "SELECT * FROM " + col.get(i).getString("TABLE_NAME") + nRow;
192 data = statement.executeQuery(query_selectrows);
193 dataRows.add(data);
194 }
195
196 return dataRows;
197 }
198
199 public static long insertRowsBatch(ArrayList<ResultSet> dataRows, int nbrRow, int nbrCol, Connection connOutput, ArrayList<ResultSet> col) throws SQLException {
200 ArrayList<String> cur_col = new ArrayList<String>();
201 long time = 0;
202 String prefix = "";
203
204 for (int i = 0; i < col.size(); i++) {
205 StringBuilder query_insertrows = new StringBuilder();
206 query_insertrows.append("INSERT INTO " + col.get(i).getString("TABLE_NAME") + " (");
207
208 while (col.get(i).next()) {
209 cur_col.add(col.get(i).getString("COLUMN_NAME"));
210 }
211
212 prefix = "";
213 for (int j = 0; j < cur_col.size() && j < nbrCol; j++) {
214 query_insertrows.append(prefix);
215 prefix = ",";
216 query_insertrows.append("[" + cur_col.get(j) + "]");
217 }
218 query_insertrows.append(") VALUES (");
219 prefix = "";
220 for (int j = 0; j < cur_col.size() && j < nbrCol; j++) {
221 query_insertrows.append(prefix);
222 prefix = ",";
223 query_insertrows.append("?");
224 }
225 query_insertrows.append(")");
226
227 PreparedStatement preparedStatement = connOutput.prepareStatement(query_insertrows.toString());
228 int count = 0;
229
230 while (dataRows.get(i).next()) {
231 long startTime = 0;
232 long endTime = 0;
233
234 for (int j = 0; j < cur_col.size() && j < nbrCol; j++) {
235 String str = dataRows.get(i).getString(cur_col.get(j));
236
237 if (str != null) {
238 preparedStatement.setString(j + 1, str);
239 } else {
240 preparedStatement.setString(j + 1, null);
241 }
242 }
243
244 startTime = System.nanoTime();
245 preparedStatement.addBatch();
246 endTime = System.nanoTime();
247
248 time += (endTime - startTime);
249
250 startTime = 0;
251 endTime = 0;
252
253 if (count > nbrRow) {
254 connOutput.setAutoCommit(false);
255 startTime = System.nanoTime();
256 preparedStatement.executeBatch();
257 endTime = System.nanoTime();
258 connOutput.setAutoCommit(true);
259
260 count = 0;
261 } else {
262 count++;
263 }
264 time += (endTime - startTime);
265 }
266 connOutput.setAutoCommit(false);
267 preparedStatement.executeBatch();
268 connOutput.setAutoCommit(true);
269 }
270
271 return time;
272 }
273
274 public static long insertRowsExUpd(ArrayList<ResultSet> dataRows, int nbrRow, int nbrCol, Connection connOutput, ArrayList<ResultSet> col) throws SQLException {
275 ArrayList<String> cur_col = new ArrayList<String>();
276 long time = 0;
277 Statement statement = connOutput.createStatement();
278 String prefix = "";
279
280 for (int i = 0; i < col.size(); i++) {
281 StringBuilder query_insertrows = new StringBuilder();
282 query_insertrows.append("INSERT INTO " + col.get(i).getString("TABLE_NAME") + " (");
283
284 while (col.get(i).next()) {
285 cur_col.add(col.get(i).getString("COLUMN_NAME"));
286 }
287
288 prefix = "";
289 for (int j = 0; j < cur_col.size() && j < nbrCol; j++) {
290 query_insertrows.append(prefix);
291 prefix = ",";
292 query_insertrows.append("[" + cur_col.get(j) + "]");
293 }
294 query_insertrows.append(") VALUES (");
295
296 StringBuilder query_insert = query_insertrows;
297 int count = 0;
298
299 while (dataRows.get(i).next()) {
300 long startTime = 0;
301 long endTime = 0;
302
303 prefix = "";
304 for (int j = 0; j < cur_col.size() && j < nbrCol; j++) {
305 String str = dataRows.get(i).getString(cur_col.get(j));
306 query_insertrows.append(prefix);
307 prefix = ",";
308 if(str != null)
309 {
310 query_insert.append("'" + str.replace("'", "''") + "'");
311 }
312 else
313 {
314 query_insert.append("''");
315 }
316 }
317 query_insert.append(")");
318
319 if(count > nbrRow)
320 {
321 startTime = System.nanoTime();
322 statement.executeUpdate(query_insert.toString());
323 endTime = System.nanoTime();
324 time += (endTime - startTime);
325 query_insert = query_insertrows;
326 count = 0;
327 query_insert.append(",(");
328 }
329 else
330 {
331 query_insert.append(",(");
332 }
333 count++;
334 }
335 }
336
337 return time;
338 }
339
340
341 public static void deleteRows(ArrayList<ResultSet> col, Connection connOutput) throws SQLException {
342 Statement statement = connOutput.createStatement();
343
344 for (int i = 0; i < col.size(); i++) {
345 String query_deleterows = "DELETE FROM " + col.get(i).getString("TABLE_NAME");
346 statement.executeUpdate(query_deleterows);
347 }
348
349 statement.close();
350 }
351
352}