OutOfMemoryError while trying to read a huge table
I do a simple thing like
SELECT id, name FROM myHugeTable ORDER by name
without any WHERE clause since I need to process the whole table somehow and get an OutOfMemoryError.
I see no reason for it, as I do not need all the data at once, so I would expect JDBC to give it to me one row after the other. I need no specials (no updates of ResultSet, no backward going, just rs.next() and simple reading), what should I do?
[444 byte] By [
Maaartina] at [2007-11-27 8:54:05]
# 1
Hope this helps:
The sql statement gets all the information from the table in a resultSet at once. It doesn't send a separate sql statement to the database each time you get an item out of the resultSet. YOu can get part of the table at a time, process it, then get the rest of the data. For example, processing the names that begin with A first, then those beginning with B, etc:
SELECT id, name FROM myHugeTable where name like 'A%' or 'a%' ORDER by name
SELECT id, name FROM myHugeTable where name like 'B%' or 'b%' ORDER by name
Also, check to ensure your computer has enough memory (at least 1Gbyte, with 2GBytes better). To check into this:
Go to <properties><advanced><enviornment variables> and add the following enviornment variable:
JAVA_OPTS -Xmx1024m
This should allow your JVM to use up a lot more memory (assuming your machine has 1Gbytes of memory).
Are you running tomcat from within Eclipse when this happens? If so, in Eclipse, from the menu go to <window><preferences><tomcat><jvm settings><append to jvm parameters><add> and enter -Xmx1024m
Next, on your desktop, try <cntr><alt><del> <task manager> <performance> and see how much memory you are using with your IDE running before you run your application, then after you run it. Buy more memory if you need it (its cheap!!!). You probably should have at least 2 Gigabtyes. Wouldn't hurt to defragment your hard disk at this time too.
# 2
> The sql statement gets all the information from the table in a resultSet at once.
But why? I do not need it, I want to switch it off, but not in a complicated way as you did, it is possible using other connection possibilities so I suppose it must be possible with JDBC too...
I have 2GB of main memory, but I will never have as much as space on my HD, so the solution is not universal enough.
Using things like "where name like 'A%'" or "LIMIT 100000" would make everything more complicated (there may be more huge tables and view) and slower. And it would me make the work I expect to be done by JDBC.
# 3
I dont believe there is a way to 'turn it off" and fetch one record at a time. The network would have to re-establish a connection and transimit the data from the database to your server for each and every record. That would take up hugh bandwidth and processing time compared to sending all the records at once. Also, I believe there is no other way.
By the way, if your table is that large, I suspect it needs to be normalized or otherwise restructured by a database administrator. Either that or you have a lot of obsolete records in the table that aren't used anymore.
I cant say how to resturcture the table since I'm not familiar with its data,
but a possible way to restructure your data would be rather than having two columns, restructure it so each record has 4 columns.
Example:
personID data1
123412
123415
123416
123512
123515
123516
change to:
personIDdata1data2 data3
123412 1516
123512 1516
Another way, is to have a comma delimited string for all values
Example:
personIDdata
1234 12,15,16
1235 12,15,16
Of course, you would have to parse the 'data' to extract the information.
Lastly;
your storing the information on your hard drive? Seems like you wrote a program to backup all the data in the database onto your harddrive. The database administrator should have a tape-backup method that comes with the database to do that without you writing a program to do it.
