I have a Derby table with up to a million rows. Some large subset of those rows may be returned by a SELECT query.
I am using IBATIS quertyForList with the embedded Derby driver. I am using the version which has a maxRows parameter. I call that method and receive back the proper “maxRows” java.sql.ResultSet objects. For example, the select would match 100,000 rows, but I only get the first 200. That all works fine.
However, I am getting OOM’s when the select would match nearly a million rows and after profiling with JProfiler the culprit seems to be millions of weakly referenced org.apache.derby.iapi.types.SQL objects (SQLLongInt, for example) that appear to be created far faster than they can be GC’ed. This particular performance test run would return a million records, of which I am only wanting to retrieve the first 200.
My questions are:
1. Is this expected behavior?
2. Is IBATIS properly using a cursor, or what is the right way to limit the number of Derby objects created (the ResultSet count is correct)
3. Are there Derby or IBATIS parameters that can be employed to correct this behavior?
Derby version is 10.5.3, IBATIS version is 220.127.116.116, and Java version is 1.6. I have cross-posted to both the Derby and IBATIS lists since I don’t know where the root problem resides.
Thanks so much for your help!