See below ..
Ignacio Coloma wrote:
>>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;
>
Unfortunately the list is ordered by a field other than the primary key
so if you had a list like ..
pk
name
3
Fleury
2 Smith
1 Sundstrom
and your last record in the current list was Fleury you would get end
of list not Smith.
You need to use limit and offset
So the first time
SELECT ID, NAME FROM TABLE
ORDER BY NAME
limit 50;
And after ..
SELECT ID, NAME FROM TABLE
ORDER BY NAME
limit 50
offset 51;
A cursor is ideal but it would have to be read-only and you would have
to keep it in is own transaction outside the current one.
> 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
