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]