• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Retrieve data from a table

 
Chan Apex
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
One of my friend asked me this question. How can i get the data @ very fast from the table if it has 1 lack of records using jdbc resultset ? please clear my doubt?

Thanks in advance.
('');
 
Christophe Verré
Sheriff
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
1 lack ?
 
Chan Apex
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
may be more than that count...
 
Chan Apex
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
A normal ResultSet data isn't retrieved until you do the next call. The ResultSet isn't a memory table, it's a cursor. Suppose the problem sorted out for the less than 1 lack records. but how to get more than that count??
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Apex,

how many is "1 lack"? I'm sort of familiar with "lakhs", which I assue is what you mean. However, if you write in terms that everyone will understand, eg. in 100.000 records, you'll probably get better responses.

You might be interested in the setFetchSize() method of the Statement class. This allows you to set number of rows that will be retrieved from the database in one call. Setting it to a value of about 100 migth be a good idea. I wouldn't set it too high, though. What we want is to establish a nice flow from the database to the client in chunks that can be easily processed.

Also check the performance of your SQL query. If you read all data from a single table, not much can be done to speed things up (depending on your database), but if the data are produced using a more complicated query, there might be ample space for improvements.

Apart from this, at least three more things can be done to speed things up:

1) Retrieve columns from the resultset by their position, not by name. It saves the translation from name to column position for every access.

2) Avoid creation of objects if possible. If you're reading ints from the database just to write them to a file, don't convert them to Integers. Nulls have to be handled separately in this case, however it can save substantial processing time (my own experience).

3) If the processing of obtained records takes some time, think about using producer-consumer desing pattern. You'd have one thread to extract the data from the resultset as quick as possible and then call the next() method, while another thread would process the data. That way you could be able to crunch the data (eg. format them and write to the file) while next batch is being retrieved from the database. Some fine-tuning with respect to the fetch size and queue size will be necessary. However this is quite a lot of work, so I'd probably not choose this path unless I was really desperate.
 
Ravi Kiran Va
Ranch Hand
Posts: 2234
Eclipse IDE Firefox Browser Redhat
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Martin , 1 lakh is related to Indians Counting.

1 Lakh means 100,000 .

As you said
You might be interested in the setFetchSize() , assume that there are 1 Lakh Records and one uses



What happens to the other 99,000 Records ?? How can these would be retrivied ??


 
Paul Clapham
Sheriff
Posts: 21416
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Nothing "happens to them" (whatever you meant by that). You get them in the normal way by reading them from the ResultSet. Have another look at the API documentation for the setFetchSize method as I think you may have misunderstood it.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic