wood burning stoves 2.0*
The moose likes JDBC and the fly likes JDBC : java heap space exception Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "JDBC : java heap space exception" Watch "JDBC : java heap space exception" New topic
Author

JDBC : java heap space exception

vishwanath nadimpally
Ranch Hand

Joined: Jan 25, 2005
Posts: 116
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

Joined: Dec 15, 2005
Posts: 333
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

Joined: Jan 25, 2005
Posts: 116
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
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

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
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

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

Joined: Jan 25, 2005
Posts: 116
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

Joined: Dec 15, 2005
Posts: 333
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

Joined: Jan 25, 2005
Posts: 116
Thanks ! will try the setFetchsize() and see if I can get the thing done...
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JDBC : java heap space exception