 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Java Forum / General / February 2006How to fetch more than 10,000 records from database using ResultSet (Java)
Hello Friends.... I have some problems regarding JDBC. If My database has More than 10,000 records in some tables. Then while retrieving (Fetch) them in one go using ResultSet object in java it gives me OutOfMemory Exception. But if i want to fetch 8,000 records,then it is fetching very well. My Problem is ==> How do I get access(Fetch) to those records using ResultSet object which has more than 10,000 in numbers ? I know there is some method called "getFetchSize" &"setFetchSize". But I don't know how to use them in combination with SQL and ResultSet object. I hope that I will get some solution from Experts like you people. Thanks. Best Regards [ vikas_khengare@yahoo.com ] vikas khengare schreef: > Hello Friends.... > I have some problems regarding JDBC. [quoted text clipped - 3 lines] > java it gives me OutOfMemory Exception. But if i want to fetch 8,000 > records,then it is fetching very well. If you create a Statement of type Resultset_Fetch_forward (see connnection.createStatement(...) ), the databasedriver or database itself will discard rows that are already read. This depends on the database and databasedriver. I believe Mysql will by default retrieve a resultset at once server-side, but maybe with fetch_forward the data will be read row by row client-side (via jdbc). See documentation of your database for mor information. > Hello Friends.... > I have some problems regarding JDBC. [quoted text clipped - 6 lines] > My Problem is ==> How do I get access(Fetch) to those records using > ResultSet object which has more than 10,000 in numbers ? java.sql.Resultset is an interface - it's not a class. Your problem is due to a particular implementation of that class, so it's hard to help you unless you say which database driver / database system you're having the problem with. Alun Harford Hi Alun In my table I have More than 10000 Records may be they are in some lakhs...... I want all of them But one bye one. When I fire SELECT query then Whether I will get all those lakhs number of record or If i get then whether I will be able to traverse through that records by using single ResultSet intherface in JAVA. I am using pure Java driver for SYBASE. Thanks Best Regards vikas.khengare@gmail.com On 2 Feb 2006 23:51:54 -0800, "vikas khengare" <vikas.khengare@gmail.com> wrote, quoted or indirectly quoted someone who said : > In my table I have More than 10000 Records may be they are in >some lakhs... for those of you not familiar with Indian units of measure: one lakh is 100,000. one crore is 10,000,000  SignatureCanadian Mind Products, Roedy Green. http://mindprod.com Java custom programming, consulting and coaching. On 2 Feb 2006 23:51:54 -0800, "vikas khengare" <vikas.khengare@gmail.com> wrote, quoted or indirectly quoted someone who said : >I want all of them But one bye one. When I fire SELECT query then >Whether I will get all those lakhs number of record or If i get then >whether I will be able to traverse through that records by using single >ResultSet intherface in JAVA. > I am using pure Java driver for SYBASE. I think the question you are asking is when I ask for large numbers of records in a result set, do I get the first one right away, or do I have to wait until it has got them all? You also might be asking if I abort the query part way through will it still go get the rest? The answers are YES, and NO. It may only get 20 at a time or so and not get the next twenty until you have consumed the previous 20, rather than getting ahead. SignatureCanadian Mind Products, Roedy Green. http://mindprod.com Java custom programming, consulting and coaching. Hi Roedy.... Thanks for reply........... No..... I can proceed with few of them..... But before first slot gets end I want second slots in my ResultSet. I want all records in every condition So I have to travel all records. There is no condition that I will get out in middle and I wont req. rest of them; So I want all records................. I want to just read all records Not to do Updarion in that........ So slot may be 20 / 200 / 2000 No problem........ But Delay wont required.... Best Regards vikas.khengare@gmail.com vikas_khengare@yahoo.com On 3 Feb 2006 04:38:49 -0800, "vikas khengare" <vikas.khengare@gmail.com> wrote, quoted or indirectly quoted someone who said : > No..... I can proceed with few of them..... But before first >slot gets end I want second slots in my ResultSet. there is nothing in the SQL standard about how it has to work. You can figure it out by testing your candidate database with code that reads one field of each result set record and immediately grabs the next. Record the times to get each record and plot a histogram of the distribution. From that you can deduce what is likely going on under the hood. See http://mindprod.com/jgloss/timeh.html SignatureCanadian Mind Products, Roedy Green. http://mindprod.com Java custom programming, consulting and coaching. > Hi Alun > > In my table I have More than 10000 Records may be they are in > some lakhs...... > I want all of them But one bye one. When you say you want the records one-by-one, if you are keeping references to all the records you already read that will give an OutOfMemoryException (given enough data). > > Hi Alun > > [quoted text clipped - 5 lines] > if you are keeping references to all the records you already read > that will give an OutOfMemoryException (given enough data). There is a rather easy solution to avoid an OutOfMemoryException when loading tables with an enormous amount of records/data, on the condition that the table has a unique key (the primary key for example). Let's assume the primary key field is called "seq". First you select the first row: select * from table where seq = (select min(seq) from table); For each following row, you select: select * from table where seq = (select min(seq) from table where seq > previousSeq); Not a pretty way of doing things, and it will hurt performance a lot, but it works. JC -- http://jcsnippets.atspace.com a collection of source code, tips and tricks Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.