• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

how to make data retrieval / query faster

 
Ranch Hand
Posts: 173
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 18944
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
the idee is to implement a paging mechanism.
for this there is a pattern
value list handler
 
Gul Khan
Ranch Hand
Posts: 173
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
 
Ranch Hand
Posts: 251
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 688
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 173
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 688
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Gravity is a harsh mistress. But this tiny ad is pretty easy to deal with:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic