aspose file tools*
The moose likes JDBC and the fly likes how to make data retrieval / query faster Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "how to make data retrieval / query faster" Watch "how to make data retrieval / query faster" New topic
Author

how to make data retrieval / query faster

Gul Khan
Ranch Hand

Joined: Sep 03, 2003
Posts: 173
Hi
I am confused about one thing. In the web context if i have a very large amount of data and i want to show it in html, it will take a very long time to retrive the data and than to show it. is there going the be a connection time out problem?
Lets say if i have more than a 100,000 records in a table that i m retrieving, what is the strategy to retrieve it and than display it. and what if i m retrieving that many records from multiple table like joins etc.
How much time range will it take to do this operation?

Any help will be very much appreciated.
Thanks alot
Gul
Anonymous
Ranch Hand

Joined: Nov 22, 2008
Posts: 18944
the idee is to implement a paging mechanism.
for this there is a pattern
value list handler
Gul Khan
Ranch Hand

Joined: Sep 03, 2003
Posts: 173
Originally posted by Benjamin Leonard:
the idee is to implement a paging mechanism.
for this there is a pattern
value list handler

i think that pattern is to how to pass the data from resultset to user, that is also one factor. What i m doign currently is make a vector of DataObjects and than pass that vector to the user for processing, saves me from rs.getXXXX() hits on to database.
But what i m thinking about is what to do when the data returned is HUGE. my select statement takes .01 seconds on MySQL table when there are 20 records in it. what if there are a 100,000 records in the table. it will take alot of time on just a simple select, not considering the joins and intersections/unions etc. How to make it fast.
Would preparedStatements help? how much would it effect the performance?
Thanks for the reply
Gul
Phil Chuang
Ranch Hand

Joined: Feb 15, 2003
Posts: 251
You should consider a few things, then:
1) limit the information retrieved to the bare minimum of information that the user needs to identify what they're looking for.
2) use "paging"
the 1st item is pretty self-explanatory - if each row is a bunch of information, then just display the key information on the list page, and provide a link that does another query to get the full information and show that on a subsequent page.
For the 2nd item, look into mySQL's LIMIT keyword - you can limit the results returned from the database by saying

which means, return 15 rows starting from row 10 (rows 10-24). So if you build the view such that a user can page through information X number of rows at a time, you won't have to worry about retrieving everything at once.
Adrian Yan
Ranch Hand

Joined: Oct 02, 2000
Posts: 688
the place you should start with is with MySQL, check your schema. MAKE SURE you have INDEX KEY, PRIMARY KEY, UNIQUE KEY set up properly. Indexing tables properly can increase database performance tremendously. Also, UNIQUE KEY and PRIMARY KEY can also increase speed.
I suggest you look thru MySQL documentation first, see how much you can imporve the performance, if the result is not satisfatory, then look into your code to deal with performance.
Gul Khan
Ranch Hand

Joined: Sep 03, 2003
Posts: 173
Thanks Phil and Adrian
I will work on that. Is it also possible to run the query and display at the same time, so the user does not has to wait until all the records are retrieved. if yes what are the pros and cons and how to do it?
Thanks alot once again for the help
Gul
Adrian Yan
Ranch Hand

Joined: Oct 02, 2000
Posts: 688
actually, you might want to take a look at JTable source code, I believe it does something similar. I remember from an article alog time ago, that JTable doesn't create all the rows if they are more than you display range. So when the user scroll the JTable, it displays them on the fly. You might want to consider that.
Since I don't know your program, I can't tell you how you are displaying the result. Of course, since your program is web based, you can break the result into several pages, forward and backward kind of deal, displaying only 100 records at a time, and generate pages on the fly.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: how to make data retrieval / query faster