# Insert large amount of data in HSQL

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];


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

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

• 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 Sep 9 '14 at 7:25
• @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. Sep 9 '14 at 9:13