Using cursors with result sets to reduce memory usage

Question

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]

Answer 1

First, try the standard JVMArguments trick, and if that doesn't work, you should use streaming via SQLResultSetOpen[]. Here's an example of this more sophisticated technique, which should enable you to interface with very large sql tables.

Example

First you can do a reality check using the GUI tool DatabaseExplorer[], making sure you've got a good connection to a valid data source:

<< DatabaseLink`
DatabaseExplorer[]

One it's looking good, instantiate the connection and make your sure not using a deprecated driver. This is what mine looks like:

conn = OpenSQLConnection[ JDBC["mysql", "localhost:3306/myDatabase"], 
  "Username" -> "root", "Password" -> ""]

Now read the results in chunks with the "MySQLStreaming" mode:

rs = SQLResultSetOpen[SQLSelect[conn, "YourTableName"], 
  "Mode" -> "MySQLStreaming"];
results = {};
While[ListQ[d = SQLResultSetRead[rs, 100, "GetAsStrings" -> True ]], 
 AppendTo[results, d]];
SQLResultSetClose[rs];

You can check the results:

results // Short
results // ByteCount

And optionally build a dataset for further analysis:

results = Flatten[results, 1]
Thread[cols -> #] & /@ results
Dataset[%]

Hope that helps!