Alessandro Baretta wrote:
I think you're trying to do something at the wrong layer of your
architecture. This task normally goes in your middleware layer, not
your database layer.
I am developing my applications in Objective Caml, and I have written
the middleware layer myself. I could easily implement a cursor-pooling
strategy...
You're trying to solve a very hard problem, and you're rewriting a lot of stuff
that's been worked on for years by teams of people. If there's any way you
switch use something like JBOSS, it might save you a lot of grief and hard work.
I eliminated this problem a different way, using what we call a "hitlist". Basically,
every query becomes a "select into", something like this:
insert into hitlist_xxxx (select id from ...)
where "xxxx" is your user's id. Once you do this, it's trivial to return each page to
the user almost instantly using offset/limit, or by adding a "ROW_NUM" column of some
sort. We manage very large hitlists -- millions of rows. Going from page 1 to page 100,000 takes
a fraction of a second.
It also has the advantage that the user can come back in a week or a month and
the results are still there.
The drawback are:
1. Before the user gets the first page, the entire query must complete.
2. You need a way to clean up old hitlists.
3. If you have tens of thousands of users, you'll have a large number of
hitlists, and you have to use tablespaces to ensure that Linux filesystem
directories don't get too large.
4. It takes space to store everyone's data. (But disk space is so cheap this
isn't much of an issue.)
You can eliminate #3 by a single shared hitlist with a column of UserID's. But
experience shows that a big shared hitlist doesn't work very well: Inserts get
slower because the UserID column must be indexed, and you can truncate
individual hitlists but you have to delete from a shared hitlist.
Craig
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
