Re: HSSF POI performance huge memory usage

[Peter Dow <petercdow@xxxxxxxxx>]

Hi Aaron,

Take a look at Giuseppe Costagliola's SQL2JXL:

http://www.mcpressonline.com/tips-techniques/microsoft/techtip-excel-flies-higher-with-jexcelapi.html

The jexcel api is much faster than the hssf api, and Giuseppe's command 
let's you use an SQL SELECT to choose your DB2 fields to be put into a 
spreadsheet.  You can also use a template spreadsheet, to which the rows 
from your SELECT statement are added.

Nothing so nice going the other way though.  I wrote a rudimentary 
XLS2PF command using the Scott K's HSSFR4 service program in an SQLRPGLE 
program; not great, but it does the job.

*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
pdow@xxxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxxx> /

On 3/2/2010 7:10 AM, Aaron Bartell wrote:
> I have had similar issues with my use of the HSSF API's (I haven't used
> Scott's) and the only way I was able to improve performance and memory
> consumption was to put Java entirely in charge of it's own garbage
> collection.  Initially I was calling the Java methods directly from RPG, but
> to fix the long run times I instead coded the entire process in Java and ran
> it in a batch job.  The process went from around 30 minutes to 3 minutes,
> and I believe it had to do with the efficiency (or lack of) between RPG and
> Java.
>
> What would be cool is if somebody would come up with a layered approach that
> would allow the RPG programmer to write cell contents to a DB2 table, then
> invoke a Java process to go through all the values in DB2 and apply them to
> a spreadsheet.  It actually wouldn't be that hard to write now that I think
> about it - very similar in nature to my RPG Chart Engine which you can check
> out if you want (free and open source:
> http://mowyourlawn.com/RPGChartEngine.html)
>
> HTH,
> Aaron Bartell
> http://mowyourlawn.com
> http://mowyourlawn.com/blog/
>
>
> On Tue, Mar 2, 2010 at 8:40 AM,<darren@xxxxxxxxx>  wrote:
>
>    
> > I'm using the HSSF POI API's, via Scott Klement's service programs, to
> > generate a rather large spreadsheet.  It has two tabs with about 20K rows,
> > and I've come to accept that it will take a little while to run.  However,
> > as I watch my job, I see 'Temporary Storage Used' go as high as 600MB.
> > I've studied the service programs, as well as my own code, to make sure
> > that I'm freeing local references to objects whenever I'm done with them.
> > Is this normal behavior for these API's with a larger spreadsheet, or do I
> > have a memory leak somewhere?
> >
> >
> >