|OutOfMemoryError [OOM error] can occur when reading large amounts of data from the database because by default, JTurbo will read all the resultset data into memory. Here are some suggestions for working around this problem:
Readers of this FAQ may also find FAQ #154 to be of interest.
- Set the cursor name on the Statement object [setCursorName(String name)], and also set the fetchSize.
There are a few different ways to set the fetchSize:
Setting the fetchSize will cause the result data to be read in chunks specified by the fetchSize parameter. This is a powerful feature for those who are reading hundreds of thousands of rows from the database and don't want to run out of memory.
- Use the JTurbo URL flag named fetchSize.
- Call Statement.setFetchSize()
Note that the fetchSize is merely a hint to the database and the database is free to ignore it. If you are calling a stored procedure that returns a resultset, setting the fetchSize may not help.
In that scenario you should consider these alternatives:
- change your code to directly use a Select statement rather than calling a stored procedure.
- consider using the TOP directive in your query "select top 100 from..."
- modify your stored procedure so that it only returns a "page" of rows at a time.
In that scenario you'd make repeated stmt.execute() calls, possibly even passing which page you want to the stored procedure.
The first code example given here might be useful.
- Increase the max heap size (RAM) available to the JVM.
- The start of this FAQ says that JTurbo will read all the resultset data into memory... but in truth (at least with JTurbo 2005 and newer) the default value for fetchSize is 100). What this means is that by default the resultset will initially contain (at most) 100 rows/records. As you loop over the resultset calling next() on it, JTurbo will read in the next "chunk" of 100 rows every 101th call to next(). If your code is accumulating all "chunks" of results into memory then that might be what's causing the OOM error. In other words it is possible that the OOM is not so much due to JTurbo as it is due to how your code is reading the data from JTurbo.
In that scenario, one possible workaround might be for your code to "spool" the resultset data to a file rather than try to accumulate all of it in memory at once.