Insert large amount of data in HSQL

5

1

I create HSQL database with one table

Needs["DatabaseLink`"]
dbLocation="c:\\work\\hsqlTest\\";
dbName="testDB";
dbServer=SQLServerLaunch[{dbName->dbLocation}]
dbConnectionObject=JDBC["HSQL(Server)","localhost/"<>dbName];
dbConnection=OpenSQLConnection[dbConnectionObject,"Name"->dbName,"Username"->"sa"];

But inserting large amount of data produce different errors with different insert. For example:

data=RandomReal[{-10,10},{500000,1}];
SQLInsert[dbConnection,"TestTableDouble",{"c1"},data];

produce error:

JDBC::error: connection exception: connection failure: java.lang.OutOfMemoryError: GC overhead limit exceeded >>

or

JDBC::error: GC overhead limit exceeded >>

How can I avoid this error?

PS: some inserts do not produce error

Add: With SQLite I have the same problem, but with inserting 1000000 rows

molekyla777

Posted 2014-09-08T11:54:14.873

Reputation: 2 728

Did you try partitioning the data and inserting it in batches? – C. E. – 2014-09-08T12:11:20.600

@Pickett Yes, after inserting some amount of data totally I get the error too – molekyla777 – 2014-09-08T12:15:57.317

1

OK, HSQL keeps all data in the memory and perhaps if you deal with big data sets it could be worth it to install MySQL or use SQLite and avoid this problem in the process. But if you really want to do this I think you can use this technique.

– C. E. – 2014-09-08T13:33:53.477

1@Pickett Agreed. You should post this as an answer. – WReach – 2014-09-08T14:33:25.950

Answers

7

Two possible solutions are

  • Use MySQL, SQLite or any other database that is not an in-memory database. HSQL keeps all its data in the memory at all times. I'm leaving this for future visitors since there is a version of HSQL that is built in that is in-memory, although that was not the case for the OP.
  • Increase the Java heap size by using the same technique that is used here (you may or may not want to put it in init.m though).

C. E.

Posted 2014-09-08T11:54:14.873

Reputation: 67 448

HSQL(Server) is not memory database, but HSQL(Memory) was. It's problem cause the JDBC connection wich suppose launching JVM for any database database driver in JDBCDriverNames[] – molekyla777 – 2014-09-09T07:25:10.777

@molekyla777 I stand corrected. JDBC needs Java and therefore JVM. Did you try increasing the Java heap size as in the link I gave you? – C. E. – 2014-09-09T09:13:53.313

yes, I try to increase Java heap size. It solve problem. Now I testing JVM with large heap size. – molekyla777 – 2014-09-09T09:22:03.073