5
$\begingroup$

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

$\endgroup$
  • $\begingroup$ Did you try partitioning the data and inserting it in batches? $\endgroup$ – C. E. Sep 8 '14 at 12:11
  • $\begingroup$ @Pickett Yes, after inserting some amount of data totally I get the error too $\endgroup$ – molekyla777 Sep 8 '14 at 12:15
  • 1
    $\begingroup$ 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. $\endgroup$ – C. E. Sep 8 '14 at 13:33
  • 1
    $\begingroup$ @Pickett Agreed. You should post this as an answer. $\endgroup$ – WReach Sep 8 '14 at 14:33
7
$\begingroup$

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).
$\endgroup$
  • $\begingroup$ 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[] $\endgroup$ – molekyla777 Sep 9 '14 at 7:25
  • $\begingroup$ @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? $\endgroup$ – C. E. Sep 9 '14 at 9:13
  • $\begingroup$ yes, I try to increase Java heap size. It solve problem. Now I testing JVM with large heap size. $\endgroup$ – molekyla777 Sep 9 '14 at 9:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.