poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bchalla...@sqli.com
Subject Re: resultset from Oracle to a HSSFSheet
Date Fri, 27 Jan 2006 15:51:28 GMT
Yes i tried
-for each row, not really usefull.
-every 500 rows for example
  -500 rows = 5 Mo space
  -before garbage, space available X
  -after garbage, space available X + 1.5Mo
  -conclusion:
    -500 rows take 3.5 Mo -> 1 row = 3500 / 500 = 7ko
    -the limit in this case for 60Mo of memory = 60 000 ko / 7 ko = 8500 rows

I'll post an other subject about HSSFRow and space.

thx tom,

Benjamin CHALLAMEL
> Have you tried running gc inside your loop?
>
> Tom
>
> bchallamel@sqli.com wrote:
>> Hello,
>>
>> Thx to Marot Laurent, Stefan and Karsten Voges.
>> 2 solutions :
>> 	- increase the heap size with –Xmx (it depends if you can or not)
>> 	- use CSV instead of Excel file for large data(more than 5000 -10 000
>> lines).
>>
>> I’ve got more informations about the problem of memory.
>>
>> I’d like to know if you think it is normal a HSSFRow takes 15-20ko for a
>> line with 25 columns?
>> I used in case
>>  a) normal settings 65 mo
>>  b) JAVA_OPTIONS="-ms128m -mx256m -XX:MaxPermSize=64m"
>>
>> This is a sample of code I’m using to test the problem of memory used:
>>
>> 	String thirdQuery = "select * from accord";
>> 	//9000 lines and 25 coloms per lines
>>
>> 	ResultSet thirdResultset >
>> SqlManager.executeQuerySelectResultSet(transaction.getConn(),
>> thirdQuery);
>> 	ResultSetMetaData metadata = thirdResultset.getMetaData();
>> 	int nbColumns = metadata.getColumnCount();
>>
>> 	System.out.println("MEMORY: Runtime.freeMemory(): " +
>> oneRuntime.freeMemory());
>> 	//a)ouput  65 000 000 (62Mo)
>> 	//b)ouput 114 000 000 (108Mo)
>>
>> 	//create the workbook with one sheet
>> 	HSSFWorkbook oneWorkBook = new HSSFWorkbook();
>> 	HSSFSheet oneSheet = oneWorkBook.createSheet();
>> 	HSSFRow oneRow = null;
>>
>> 	//loop to set the value
>> 	int index = 0;
>> 	while(thirdResultset.next()){
>> 		oneRow = oneSheet.createRow(index);
>> 		for(int i=1;i<=nbColumns;i++){
>> 			oneRow.createCell((short)(i-1)).setCellValue(thirdResultset.getString(i));
>> 		}
>> 		oneRow=null;
>> 		index++;
>> 		System.out.println("MEMORY: -"+index+"- Runtime.freeMemory(): " +
>> oneRuntime.freeMemory());
>> 		//each line contains 25 columns, loosing 15ko-20ko per line
>> 	}
>> 	//a)out of memory exception before the end of the loop lines 6000
>> 	//b) ok ouput 9000 lines in the Excel file
>>
>> Benjamin,
>>
>>
>>
>>>just increase your heap size with -Xmx
>>>
>>>
>>>Laurent Marot
>>>_______________________________
>>>
>>>ALLIACOM
>>>98 Avenue du Général Leclerc
>>>92 100 Boulogne Billancourt - France
>>>Phone +33 (0)1 55 38 98 98
>>>Mobile 06 21 09 52 29
>>>www.alliacom.com
>>>
>>>
>>>-----Message d'origine-----
>>>De : bchallamel@sqli.com [mailto:bchallamel@sqli.com]
>>>Envoyé : mercredi 25 janvier 2006 12:40
>>>À : poi-user@jakarta.apache.org
>>>Objet : resultset from Oracle to a HSSFSheet
>>>
>>>java.lang.OutOfMemoryError
>>>
>>>Hello,
>>>
>>>I'm getting a resultset from Oracle.
>>>I need to get colums name and the resultset in a HSSFSheet of my
>>>HSSFWorkbook.
>>>It will looks like toad view data (top-> colums name and data after).
>>>I create an arraylist of arraylist
>>>	1)the colums name for the first arraylist,
>>>	2)data
>>>After i loop into the arraysList of arrayList, and i tried to set the
>>>value of the entire HSSFSheet with this method:
>>>	oneSheet = workBook.getSheetAt(sheetNb);
>>>	oneRow = oneSheet.getRow(rowNb);
>>>	if (oneRow == null) {
>>>		oneRow = oneSheet.createRow(rowNb);
>>>	}
>>>	oneCell = oneRow.getCell((short) cellNb);
>>>	if (oneCell == null) {
>>>		oneCell = oneRow.createCell((short) cellNb);
>>>	}
>>>	oneCell.setCellValue(stringValue);
>>>
>>>There are just 46 000 rows and for each rows 25 cells.
>>>
>>>At the end i have this exception:
>>>java.lang.OutOfMemoryError
>>>
>>>Do you know an other way to set the value of a HSSFSheet from an
>>>ArrayList, Hashmap, Array?
>>>
>>>Thx
>>>
>>>benjamin CHALLAMEL,
>>>
>>>
>>>---------------------------------------------------------------------
>>>To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
>>>Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
>>>The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/
>>>
>>>
>>>---------------------------------------------------------------------
>>>To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
>>>Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
>>>The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
>> Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
>> The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/
>>
>>
>
>
>


---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List:     http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project:  http://jakarta.apache.org/poi/


Mime
View raw message