[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 930
  • Last Modified:

JDBC Resultset Cache

I have a swing app that displays a JTable whose underlying data comes from a JDBC resultset.  The app runs fast on the local network, but if we use it across the internet (at 60KBytes/Sec speed max), it is slow, even to scroll left to right.

What ways can I speed up the app?

It is understandable that scrolling up/down the table can be a little slow if necessary, but left and right should be fast.  I figure I should put a jdbc resultset cache, and have seen discussions about CachedRowSet.  I'm not sure what I have to download to make that work.  And if there are other ways to cache.

I'm open to other ideas and solutions, so long as I don't have to rewrite the entire app and turn it upside down!
0
amp834
Asked:
amp834
  • 7
  • 6
  • 5
  • +1
4 Solutions
 
for_yanCommented:

Does not seem that you need to download anything special - here they populate
it from ResultSet:
http://www.javaorigin.com/tutorials/59/23/80/Using-CachedRowSet

Do you mean that you have a huge result set underlying your table
where you show just a window on your result set at any time?

Or it is more or less finite JTable.
If so, maybe better to just have it in array or vector of vectors?

0
 
objectsCommented:
>  I figure I should put a jdbc resultset cache, and have seen discussions about CachedRowSet.

you don't need that.

By the sound of it your problem is caused by your model querying the database to get table values.
Instead you should query the database one to get the table data, and use that data to populate your table model
0
 
amp834Author Commented:
From reading the article, CachedRowSet is not really a cache, it's a complete copy.  (A cache would keep recently accessed data locally to speed up the operations).

My current table model does one query, and uses the resultset to get table values.  The query can return, say, 30,000 records, so I wouldn't want to download 30k records, just 12 or 15 to fit the screen (and maybe a little more in the background, but that would be more advanced!).

I'm looking for a simple way of doing this.  My resultset has record #'s, so I can always cache the results manually (look to the cache before reading the resultset), but don't want to reinvent the wheel.  There may be something that will do this more transparently.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
for_yanCommented:
Unless there is invented "wheel", you may probably want to look at some manual caching
of rows especially if your users are normally not scrolling too dramatically, so you select some rows around the cursor, and then go to
 db only when they move significantly out of range - that would proabably provide reasonable experience.
0
 
amp834Author Commented:
It's the pre-invented wheels that I'm looking for.

For example, the resultset provides many routines--getInt, getXXX, putXXX, etc., I certainly don't want to provide all of these routines.  And solve all the gotcha's that others came up with.


Perhaps I should also ask a question from another forum, maybe java EE or network people would have ideas how to speed the app up (possibly without even changing the code!)

Any ideas?
0
 
for_yanCommented:
Or maybe database peope also

0
 
for_yanCommented:
Maybe you need to check if you have appropriate indexes on the table?
0
 
amp834Author Commented:
It's not the indexes, it runs very well on a local network, it already has the resultset.  The scrolling through the resultset is slow.
0
 
objectsCommented:
> My current table model does one query, and uses the resultset to get table values.

That sounds like your problem.
You should be querying the database (in the background) as needed. For example loading a 'page' at a time as needed.

> (possibly without even changing the code!)

maybe, but by the sounds of it the problems in your code
0
 
objectsCommented:
> The scrolling through the resultset is slow.

scrolling thru a resultset over the network *will* be slow
0
 
for_yanCommented:
I'm not sure of the details but your big resultsets will not be sitting on your local machine I believe, it all depends on how driver accomplishes it. Maybe somehwere on that level there is some resource to speed it up. Otherwise iterating through resultset shouldn't be longer thathrough hashtable.
0
 
for_yanCommented:
So how do you deliver the data for a particular cell of the table? Maybe you can post one of the methods?
0
 
objectsCommented:
0
 
objectsCommented:
Just been discussing your issue here and depending on exactly how you are using your ResuultSet and what db/driver you are using you may be able to improve performance by setting the fetch size for the result set. Give that a try and see how it goes.
0
 
amp834Author Commented:
The tablemodel and the fetch size look promising and simple, I will try these and report back.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I've requested that this question be deleted for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
 
amp834Author Commented:
I will close this question myself and award points.  Please cancel the "auto cleanup".
0
 
amp834Author Commented:
I will close this question myself and award points.  Please cancel the auto-cleanup.
0
 
amp834Author Commented:
I will manually cache the resultset, it will probably take 3-4 hours of tinkering.  Thanks for your suggestions, they helped me on deciding this, and I will probably use tweak some of the parameters you mentioned.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 7
  • 6
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now