I have a loop which performs SQLConnections and fetches data. Sometimes, I get an error due to overhead limit with a message saying JDBC:GC overhead limit exceeded.
This lead to disturbance of the iteration and some times stops the whole calculation.
Is there any way to check the size or length of the data before fetching (and before the error happens) so that if the length or the size exceeds a certain limit I can force the loop to skip this iteration.
If you have a potentially large amount of data that you need to process in batches then you may use Result Sets functions. These basically create and navigate a SQL cursor of your SQL SELECT statement.
Using the example databases that ship with Mathematica (see Using the Example Databases tutorial).
Needs["DatabaseLink`"]
conn = OpenSQLConnection["publisher"];
rs represents a cursor created in the database. The Result Sets tutorial demonstrates several functions including SQLResultSetRead. This can be placed in a loop to iterate over the dataset and process each batch.
rs = SQLResultSetOpen[SQLSelect[conn, "SALES"]]
res = 0;
While[(data = SQLResultSetRead[rs, 5]) =!= Null,
res += Length[data]
];
SQLResultSetClose[rs];
res
(* 16 *)
This inner-loop would replace your current bulk selection and processing of each iteration. The above reads in 5 records at a time from the "SALES" table with the batch process just summing up the number of records.
CloseSQLConnection[conn];
There are quite a few different types of SQL cursors that SQLResultSetOpen can create. I strongly suggest reading the entire tutorial and have a read of cursors behaviour from your database management system's documentation. For example, in some systems they are resource heavy and should not remain open for long.
Hope this helps.
