I have a database table with 900,000 rows and 80 columns per row which I'd like to work with in mathematica. My computations are isolated to each row, so I'd like to stream only a single row (or small number of rows) at a time.
SQLSelect naturally loads the entire results into memory causing a java heap error in my JDBC driver.
My initial thought was to use result sets and cursor through them. However, after trying a number of approaches, the JDBC driver (PostgreSQL) and mathematica still try to load the entire results into memory, causing the JDBC driver to run out of memory each attempt.
I can use
SQLExecute to setup a cursor manually, but this loses composability.
How can I force Mathematica's
SQLResultSets to use a cursor and work in a memory efficient way? From reading the documentation I've setup my
Resultset correctly, the only thing I can think of that might be missing is forcing
Autocommit off, which I can't figure out how to do.
Here's my code.
Needs["DatabaseLink`"]; dbconn = OpenSQLConnection[JDBC["PostgreSQL", "localhost/mydatabase"]] (* Don't know how to turn off Auto-commit, this may be what is missing here *) rs = SQLResultSetOpen[SQLSelect[dbconn, "mytable"], "FetchDirection" -> "Forward", "Mode" -> "ForwardOnly", "FetchSize" -> 100] SQLResultSetRead[rs] SQLResultSetClose[rs]