Kristian, The query is approximately this:
SELECT * FROM t WHERE (some criteria) ORDER BY y; 'y' is defined as a foreign key so has an index generated. Could it be that Derby is bringing the rows internal to do the sorting? As for maximum page cache size, it’s the default. Is there a good reference for Derby performance tuning, because I sure could use some help with that. It is currently being run as an embedded server with vm parameters -Xmx256m. I will attempt running the command via ODBC directly, with the FETCH syntax, and produce a heap histogram (is there anything in particular you are looking for or a specific tool output -- I generally use jprofiler). Thanks so much for your help! Eric -----Original Message----- From: kristian.waa...@sun.com [mailto:kristian.waa...@sun.com] Sent: Wednesday, November 18, 2009 2:50 AM To: Derby Discussion Cc: user-j...@ibatis.apache.org Subject: Re: OOM with millions of weakly-referenced Derby objects Larry Meadors wrote: > The expected behavior is really dependent upon the underlying JDBC driver. > > Using the maxRows parameter is a last resort option, IMO. > > Limit the results with the SQL if at all possible, here's an example: > > http://old.nabble.com/top-N-reporting-with-derby-td17221545.html > Hi, Derby now supports the OFFSET/FETCH clause. See [1] for some examples, or consult the reference manual. I think some work is also being done on improving the ROW_NUMBER implementation. Regarding your OOM, that is generally not to be expected. However, it is hard to say if it's a Derby bug without knowing what is going on in your application, and how Derby is configured. For instance, what is your maximum page cache size set to, and what size is the heap of the JVM? Posting the heap histogram might give us some clues. Have you tried reproducing this without iBATIS? Regards, -- Kristian [1] http://blogs.sun.com/kah/entry/derby_10_5_preview_fetch > Larry > > > On Tue, Nov 17, 2009 at 11:42 AM, Eric Floehr <eric.flo...@3x.com> wrote: > >> All, >> >> >> >> 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 2.3.4.726, 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! >> >> >> >> Best Regards, >> >> Eric >> >> >> --------------------------------------------------------------------- To unsubscribe, e-mail: user-java-unsubscr...@ibatis.apache.org For additional commands, e-mail: user-java-h...@ibatis.apache.org
