• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JDBC : java heap space exception

 
vishwanath nadimpally
Ranch Hand
Posts: 116
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am getting a heap space exception when I am trying to run a sql query that fetches me close to 30,000 rows using JDBC. I looked up the docs and found there is a setfetchsize(), but that doesn't really serve my purpose because I would definitely need the 30,000 rows to write to a txt file.

Is there a way to get around this?
How can I set the java heap size for tomcat 5.5.12?

Thanks for any suggestions.
 
stu derby
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
1. http://www.chemaxon.com/jchem/doc/admin/tomcat.html

2. Many people arrange their code so that they don't have to bring the entire ResultSet into memory at one time, e.g.
 
vishwanath nadimpally
Ranch Hand
Posts: 116
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks stu. I changed my Catalina.sh to have 'Xmx500M' for the heap size, but now the process runs forever.I know inserting 30,000 rows shouldn't take this long.

I need the complete result set because I write the rows in to a txt file and massage the data thru shell script inside a scheduled kron job.

Is there anything else I can do for improving the performance?
 
Paul Clapham
Sheriff
Posts: 21322
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by vishwanath nadimpally:
I need the complete result set because I write the rows in to a txt file and massage the data thru shell script inside a scheduled kron job.
To me, that doesn't explain why you have to store all the data in your program before you write it to the text file. It sounds like the opposite -- you are writing the data to the text file, THEN the shell script massages the file. (And the fact that this is happening in a cron job should be irrelevant.)
 
Paul Clapham
Sheriff
Posts: 21322
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
And... if this is happening in a cron job, what does Tomcat have to do with it? Cron sounds much more plausible, dumping thousands of records from a database to a text file really doesn't sound like something a servlet should be doing.
 
vishwanath nadimpally
Ranch Hand
Posts: 116
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Paul Clapham:
To me, that doesn't explain why you have to store all the data in your program before you write it to the text file. It sounds like the opposite -- you are writing the data to the text file, THEN the shell script massages the file. (And the fact that this is happening in a cron job should be irrelevant.)


You are rite. May be I was not clear but this is waht I am doing,

Step 1) I query the db and get a rs. I write this rs to a .txt file (P_TABLENAME.txt).I write it in a certain format.
Step 2) I read from the txt file and delete the duplicate rows in the target tables using a cron job.




This cron job is scheduled.

But Step1 is a link on a jsp through which I get the resultset. This is where I run out of memory.
 
stu derby
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You may need to use setFetchSize, or stop using some non-defaults.

setFetchSize does not truncate the result set; it controls the number of rows that are brought into memory at one time, for buffering the network I/O to the database. Oracle JDBC by default uses a fetch size of 10; you will get better performance with a larger fetch size, but you don't want to set it to high for memory reasons, and the performance benefit tails off with large values too.

Also, you need to use the default ResultSet type, TYPE_FORWARD_ONLY, and default concurrency, CONCUR_READ_ONLY; the easiest way to use the defaults is to not specify a ResultSet type or a concurrency when the statement is created; you should only override the defaults when you specifically need the feature.
 
vishwanath nadimpally
Ranch Hand
Posts: 116
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks ! will try the setFetchsize() and see if I can get the thing done...
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic