The moose likes JDBC and Relational Databases and the fly likes Retrieve data from a table Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Retrieve data from a table" Watch "Retrieve data from a table" New topic

Retrieve data from a table

Chan Apex

Joined: Oct 26, 2010
Posts: 16
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é

Joined: Nov 24, 2005
Posts: 14688

1 lack ?

[My Blog]
All roads lead to JavaRanch
Chan Apex

Joined: Oct 26, 2010
Posts: 16
may be more than that count...
Chan Apex

Joined: Oct 26, 2010
Posts: 16
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

Joined: Aug 22, 2010
Posts: 3733


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

Joined: Apr 18, 2009
Posts: 2234

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 ??

Save India From Corruption - Anna Hazare.
Paul Clapham

Joined: Oct 14, 2005
Posts: 19973

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.
I agree. Here's the link:
subject: Retrieve data from a table
It's not a secret anymore!