Joost Kraaijeveld a écrit :

Did you have created an index on the column which contains the value ?
If an index is created, the query should be much more fast

Joost Kraaijeveld a écrit :

You can also simplfy the query to do
SELECT * FROM MY_TABLE;

If the driver give you a scrollable ResultSet, you can do
ResultSet rs = stmt.executeQuery (qry);
rs.absolute(100000); // this will go to the line 100 of the table,
without reading the 100000 element

I think the underlying problem is that you are abusing the SQL database.  A SQL
"table" isn't a table in the same sense as say the concept underlying a JTable.
It doesn't consist of /numbered/ rows.  SQL purists will tell you that the rows
are in fact unordered, and in practical terms if you push the database hard
enough (as you are doing) they are right.

What /is/ ordered is the set (or sequence) of results returned by one actual
query.  But the DB has no way to tell what will be the 5000000-th row in that
sequence except by /counting/ the rows.  (This is implementation-dependent of
course, but it's more likely to be true than not, and is true of PostgreSQL in
my -- limited -- experience).

So one basic, and probably incurable, problem you have is that finding the N-th
row is infeasible for large N.  A possible workaround for that would be to
change the UI a bit and /start/ with the row of interest rather than trying to
capture a group of rows around it.  In that case you would issue a "select
<whatever> where <whatever> >= ?" and use the next 200 rows (actually you'd
probably restrict the number of rows in the query itself, but I can't remember
the syntax offhand).   An alternative workaround would be to include actual row
numbers as an indexed field in the database itself, but (a) that's a stupid
abuse of the data to support a broken UI, and (b) inserts and deletes would
become all-but-impossible.

Of course that will mean changing the UI that the customer has asked for.  But
then you'll have to do that anyway.  At least, you do if the idea is to provide
the illusion of a JTable which contains /all/ the 1000000 rows -- that is
simply Too Many for a scrolling interface (even if you could fix the
performance problems).   If that /is/ what the customer is asking for then I
suggest you create a mock-up with dynamically-generated data filling a virtual

BTW, we discussed approximately this issue in June/July; you might find it
helpful to review the thread entitled "How to populate a very large recordset
into JTable?".

   -- chris