Problem in huge result set
I have problem in using huge result set.
I'm making report application which the output is Ms. Excel (using POI), and the report can consist thousands of rows and may columns. I have to fetch it, process it, and make a beautiful Ms. Excel report file.
The problem is, when I fetch too many row from database, it always have "java.lang.OutOfMemoryError: Java heap space" error.
Can I fetch part by part data from database instead of fetching all data to result set?
# 1
There is only one solution in this case
Use Stored Procedure in Database
but unfornatunly MS Excel do not support StoredProcedure
in Stored Procedure u can Short Data And Send into Application
which prints report
IF u r showing all data in a single report then it may create problem to showing data in a page
u can use indexing through u can work with limited data show
Message was edited by:
skpmca
# 2
I think the problem is not that you are reading many records from the database. (You are using a forward-only result set so that you don't make the driver store rows unnecessarily, aren't you?) I think the problem is that you are producing a very large spreadsheet which takes up a lot of memory. And I don't think there's any way around that, using POI, except to give your program more memory to work in.
# 4
Exactly, yes, my application producing a huge spreadsheet report consists of several worksheet and thousands rows each worksheet.
The problem I'm facing is while the result set store all the query results (which is huge), the POI consuming memory since the result set data will be processed and written to spreadsheet, before it finally stream to file (finally...).
So, no other solution except allocating more memory to the application?
# 5
For the result set problem you might be able to fetch data in several queries instead of querying all the data at once.
Also I thaink that most JDBC implementations does not load all the data of the result set in to memory. They fetch the data from the DBMS chunk by chunk on demand. Are you sure that you are not copying all the data of the result set in to some data structure in memory?. If you do then try to have only a part of that in the memory at a time.
For the excel sheet sitting on the memory all the time I dont think that you can do much about it so anyway you will have to give it all the memory you can spare.
# 6
> Also I thaink that most JDBC implementations does not
> load all the data of the result set in to memory.
> They fetch the data from the DBMS chunk by chunk on
> demand.
This is a good point. Some implementations do load all of the data unless you tell them to load it in chunks, by calling the setFetchSize() on the Statement object from which you produce the ResultSet.
