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.