You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by bc...@sqli.com on 2006/01/26 11:05:55 UTC
RE: resultset from Oracle to a HSSFSheet
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).
Ive got more informations about the problem of memory.
Id 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 Im 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/
Re: resultset from Oracle to a HSSFSheet
Posted by bc...@sqli.com.
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).
>>
>> Ive got more informations about the problem of memory.
>>
>> Id 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 Im 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/
Re: resultset from Oracle to a HSSFSheet
Posted by Tom Schindl <to...@gmx.at>.
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/
>
>