Hi
Also have a look at the class org.h2.tools.Csv in the method initRead()
and modify the line
in = new BufferedInputStream(in, Constants.IO_BUFFER_SIZE);
to read
in = new BufferedInputStream(in, 1 * 1024 * 1024); // 1 MB
And let us know how it goes.
I have noticed that Windows in particular performs much better if you read in
very large chunks.
Regards, Noel.
Michal Bergmann wrote:
> 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 盜/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.
