Another problem is getting the ordering right, it is not a simple problem to
write the SQL to get the correct next row for every database for arbitrary
queries, but for a fixed query in a finder this might not be so much of an
obstacle. Also during the intervening time other transactions could change
the contents of the list if everything isn't locked down.
Of course since most users would not actually want to process 1M rows,
returing small pages of 50 at a time is probably pretty reasonable. Much
better than returning all 1M if they are just interested in the first 100.
Cheers
-----Original Message-----
From: Ignacio Coloma
To: [EMAIL PROTECTED]
Sent: 9/24/01 3:00 PM
Subject: RE: [JBoss-dev] EJB/QL - JBoss extentions
> Dave Smith wrote:
> > Keep the result set open, retieve from the set as necessary,
probably
> > using a cursor. That way if someone is doing a search that has
> 1 million
> > records and after I display the first 10 and the client chooses the
3rd
> > one, we have only created 10 objects.
> No, no, no! We can't keep the result set open!
> 1) having open result set and open Connection for each client,
> we'll end up with too many open connections and too many open result
> sets (256 open cursors by default in Oracle).
> 2) How about transaction bounds? Can you keep cursor open after
> transaction ends? I don't think so.
What about making an automatic paging? Think about something LIKE an
open
cursor. I'll try to be brief. Suppose this query to return 1,000,000
records:
SELECT ID, NAME FROM TABLE
ORDER BY NAME;
Suppose that you make a PagedCollection (configurable from
jbosscmp-jdbc.xml) that only get the first 50 records (and you close the
resultset). You show them. When someone tries to get the #51 record and
so
you make another call (possibly in another new Transaction context)
SELECT ID, NAME FROM TABLE
WHERE ID > #the ID of record 50#
ORDER BY NAME;
And so on. It would be more effective than any solution the programmer
could
make by hand. Are there Transaction consecuences on this? I could be
missing
something.
My 2c.
_______________________________________________
Jboss-development mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/jboss-development
_______________________________________________
Jboss-development mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/jboss-development
