fast scroll for a table ?!
Hello, I'm kinda new to Java.
My problem is with the scrollbar from a JScrollPane which contains a JTable. It's SLOW when I have too many rows to display.
The problem is related with the way I use getValueAt(row, column)
I used two ways until now, for taking data from a .mdb file:
- caching it in an ArrayList of Arraylists
- directly from a resultset
The first case is fast and nice, but for a larger database... I had a long caching time at the begining (10-30 seconds, depends on PC), other than that, there are many problems when multiple users use the same database (deleting rows).
The second case, working directly on resultset, gives me a lot of advantages (updating without problems, sorting fast, etc.) but there is a big problem, when I scroll to the end of the scrollbar the performance is poor, even on a good PC. I have a resultset with 6.000 rows and I use rs.absolute(row) to get the row I want in the getValueAt() method.
Now, the strange part is... first part of the scrollbar (let's say 1500 rows) is fast scrollable.
In my own tests I saw that it's all about the position of the row in the resultset. (I tested with rs.absolute(1) for entire table and I had a fast scroll from the begining to the end, after that I used rs.absolute(5000) for entire table and I had poor performance from begining to the end)
Now, can u give me a solution to this problem?
I think about the posibility to use multiple resultsets (with max 1000 rows) but something is bugged about that, I really don't know how to get from the query a resultset limited at 1000 rows (tried LIMIT, TOP, etc. without succes)
PS: I compared the way grids are repainted in C# or C++ when you move the scrollbar continuous. The table is not completly repainted when you move the scrollbar fast enough. So... do you have any ideas how can I implement that in java? (I preffer a fast scrollbar rather than a complete update on every scrollbar event).
Message was edited by:
Cracu
[2058 byte] By [
Cracua] at [2007-11-27 8:00:57]
# 1
No user can handle more than a list of about 30 or so items shown on the display at any given time, not to mention 6000. Thier eyes would explode and fall out of thier heads. I suggest instead you add a filter textfield to the page to limit what data is returned. For instance, instead of showing all names in a telephone book, your fillter will allow entry of 'A' through 'Z'. If they enter 'B', then only people with a last name beginning with B is shown. Similiarly, your sql statement will contain that filter to limit what records it returns. For example:'select * from Person where lastName like 'B%'. The key here is to always have a 'where' clause in your sql statement.
If you still need to have large data sets, I suggest you have a primary key of type int or long for the tabe, then fetch just the primary keys based on your 'where' clause criteria, sorted by primary key. Example:
select * from person where lastName like 'B%' sort by person_id;
Then use the list of person_ids to get the first part of the list, then the last part. Example: for person_id= 1,2,3,4,6,33,34,35,36,37,
select * from person where person_id>=1 and person_id<=6
select * from person where person_id>=33 and person_id<=37.
Then on the user screen, say you found 1000 records, displayed below are the first 100. Do you want to display the next 100? if so, you have that person_id primary key list to fetch more records from the database.
# 2
Here's some additional thoughts:
Your web page should show only the information that in necessary for the end-user to to his job. Therefore you dont show all the columns of a table(s), but just those columns the user will be interested in. This will reduce the amount of information the browser has to handle (better to show 8 columns of data with 100 records than 25 columns with 100 records).
Also, dont show the primary key of the table on the page (the person reading the page has no use for the primary key). Make that field 'hidden' so its not shown to the end user, but is still sent back to the server when the page is submitted. Instead of showing the primary key in a column, show the first, middle, and last name of the person who's primary key that is (you'll need to join your table with the person's table to do so).
You'll probably have to interview the end users to determine what columns they want displayed on the form.
Lastly, if you have 3 filter textfields (say, fetch by lastName, state, and age), allow the state and age fields to be blank (not part of the filtering), but the lastName mandatory (show an error message if they try to leave it blank).
# 3
Thank you for reply.
It's not about a webpage, it's a stand alone application for a real estate agency.
Like you said, in my table I had only the most important columns, 8 of 25.
Also, I have to mention that application is usable even with that slow scollbar.
Indeed, I don't need entire database in my table, in the worst case... something close to 1000 rows, which is fast enough.
BUT... I still want that fast scroll bar when I have 6k rows in my table (or more), and not because I'm obstinate, but I saw that is possible in a similar application. (when they fast scroll a table, they don't update entire viewport, unless you are moving slow enough or release the mouse button)
Using standard JScrollPane... when you fast scroll, you have to wait until a viewport is completely draw, for every single scrollbar change, and considering that moving cursor in ResultSet with rs.absolute(rowIndex) is slow for a rowIndex greater than 200... I have that delay.
There should be a way to remove/rewrite the handler for scrollBar change event. Something like... when scrollbar is used: it should start updating the viewport, and if you continue to move the scrollbar, it should cancel the process and restart update method for the new position of the scrollbar.
Thanx anyway for reading. I'll try to find a way.
Cracua at 2007-7-12 19:43:01 >
