Splitting input file into multiple pieces and importing each one in
new thread also helps to improve performance. Importing 5M records in
5 threads on my laptop took csvread: 40276 ms 8,055 µs/op. Decent
desktop machine should be able to import 10M records in one minute.
Here is an example ...
public class TestCsvRead {
public static void main(String[] args) throws SQLException,
IOException, InterruptedException {
int recordCount = 1000000;
int threadCount = 5;
for(int j = 0;j<threadCount;j++) {
BufferedWriter bw = new BufferedWriter(new FileWriter("c:/
TESTTABLE"+j+".csv"));
bw.write("#COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8,
COL9, COL10, COL11, COL12, COL13, COL14\n");
for(int i = 0;i<recordCount/2;i++) {
bw.write("62.2,39.2,56.3,110.6,77.8,94,86,63.9,64.6,95.3,68,702,3,2010-12-01\n");
bw.write("61.4,67.1,52.6,103.1,50,77.2,51,81.8,71.4,47.6,19.4,714,1,2010-12-01\n");
}
bw.close();
}
final String url0 = "jdbc:h2:c:/
testcsv;LOG=0;CACHE_SIZE=65536;LOCK_MODE=0;UNDO_LOG=0;MULTI_THREADED=1";
final String url = "jdbc:h2:c:/testcsv";
Connection conn = DriverManager.getConnection(url0);
Statement stmt = conn.createStatement();
for(int i = 0;i<threadCount;i++) {
stmt.execute("DROP TABLE IF EXISTS TESTTABLE"+i);
}
TC tc = new TC();
List<Thread> threads = new ArrayList<Thread>();
for (int i = 0; i < threadCount; i++) {
final int fi = i;
Thread e = new Thread() {
@Override public void run() {
try {
Connection conn =
DriverManager.getConnection(url);
Statement stmt = conn.createStatement();
stmt.execute("CREATE TABLE TESTTABLE"+fi
+"( COL1 DOUBLE, COL2 DOUBLE, COL3 DOUBLE, COL4 DOUBLE, COL5
DOUBLE, COL6 DOUBLE, COL7 DOUBLE, COL8 DOUBLE, COL9
DOUBLE, COL10 DOUBLE, COL11 DOUBLE, COL12 INT, COL13 INT, COL14
TIMESTAMP) AS SELECT * FROM CSVREAD('c:/TESTTABLE"+fi+".csv','COL1,
COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10, COL11, COL12,
COL13,COL1','lineComment=#');");
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
};
e.start();
threads.add(e);
}
for (int i = 0; i < threadCount; i++) {
threads.get(i).join();
}
stmt.execute("SHUTDOWN");
tc.stop("csvread", recordCount*threadCount);
}
}
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
