Comments - How to Quickly Insert Data Into MariaDB

BEGIN; INSERT ... INSERT ... END; BEGIN; INSERT ... INSERT ... END; ...

------------------------------------------------ I tried to use above concept in java while inserting records in mariadb.

if in Linux system A(Ram size 4 GB) it takes 15 seconds to inserts 20000 records then in Linux system B(Ram size 16 GB) it takes about 2 minutes 55 seconds. you can see how much it slow is in case of Linux system B. though B has got more RAM size than A.

insertion instructions : for any insertion instruction it give same result, for example you can see bellow. i think its not issue of insertion instruction.

String insertQuery = "insert into vm_missedcalls (SUBSCRIBER,CALLER,CALLEDTIME,PROCESSED,REASON) values (?,'2222222222',sysdate(),'N','U')";

Please find bellow the sample code to insert 20000 records. I have used BEGIN and END statement but its not working properly. Please see the comment at line pStmt.execute(end);

? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 public static void insertIntoVM_MISSEDCALLS(){

Connection con; PreparedStatement pStmt = null; String begin = "BEGIN"; String end = "END";

try { Class.forName("org.mariadb.jdbc.Driver"); con = DriverManager.getConnection(ss,"root","onmobile");

String insertQuery = "insert into vm_missedcalls (SUBSCRIBER,CALLER,CALLEDTIME,PROCESSED,REASON) values (?,'2222222222',sysdate(),'N','U')";

pStmt = con.prepareStatement(insertQuery);

String query;

StringBuilder subsStart = new StringBuilder("11"); String subscriber;

for(int i = 0 ;i<1;i++){ for(int j = 0 ;j<1;j++){ for(int k = 0 ;k<1;k++){

inserting 20000 records for(int l = 0 ;l<2;l++){ for(int m = 0 ;m<10;m++){

pStmt.execute(begin); at each 1000 insertion

for(int n = 0 ;n<10;n++){ for(int o = 0 ;o<10;o++){ for(int p = 0 ;p<10;p++){ subscriber = subsStart.append(i).append(j).append(k).append(l).append(m).append(n).append(o).append(p).toString();

pStmt.setString(1, subscriber);

pStmt.execute();

subsStart = new StringBuilder("11");

} } }

pStmt.execute(end); due to this line it throw exception [see bottom part for exception] if i comment this line the only 19000 records are inserted into DB }

} } } }

} catch (SQLException e) { } catch (ClassNotFoundException e) {

}finally{ closeStatement(pStmt); }

}

// Exception thrown java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1 Query is: END at org.mariadb.jdbc.internal.SQLExceptionMapper.get(SQLExceptionMapper.java:138) at org.mariadb.jdbc.internal.SQLExceptionMapper.throwException(SQLExceptionMapper.java:106) at org.mariadb.jdbc.MySQLStatement.executeQueryEpilog(MySQLStatement.java:266) at org.mariadb.jdbc.MySQLStatement.execute(MySQLStatement.java:289) at org.mariadb.jdbc.MySQLStatement.execute(MySQLStatement.java:338) at old.codes.InsertLessSubscribers.insertIntoVM_MISSEDCALLS(InsertLessSubscribers.java:156) at old.codes.InsertLessSubscribers.main(InsertLessSubscribers.java:32) Caused by: org.mariadb.jdbc.internal.common.QueryException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1 Query is: END