2
$\begingroup$

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.

$\endgroup$
  • $\begingroup$ Can't you do that in SQL? Put your selection in a temporary table, then count its size and return the table if it is small or some error code if it is large. $\endgroup$ – Sjoerd C. de Vries Sep 10 '16 at 6:24
  • $\begingroup$ @SjoerdC.deVries unfortunately I don't have any control on the SQL database. I am denied from any editing . Second, I don't know anything about SQL Programing . $\endgroup$ – Algohi Sep 10 '16 at 11:03
3
$\begingroup$

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.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.