This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
To: Joachim Zobel <nc-zobeljo@netcologne.de>
From: Tim Bunce <Tim.Bunce@pobox.com>
Subject: Re: Just while we are so nicely [OT]: SQL Search
Results in pages
Date: Thu, 2 Aug 2001 14:11:04 +0100
On Thu, Aug 02, 2001 at 01:07:34PM +0200, Joachim Zobel wrote:
> Hi.
>
> One of the really nice features of MySQL (OK, its not a "real" RDBMS, but
> who cares:) is LIMIT.
>
> Using Oracle I have found it a real pain to display search results in pages
> (of eg 20) while using connection pooling. The problem is that you can not
> be shure to repeat the same resultset on every page. This gets a real
> problem if the queries get expensive and I can't afford sorting because it
> destroys first row performance.
>
> Is there a generic soloution to this? Has anybody found this worth writing
> a pattern?
This may be of interest:
http://search.cpan.org/doc/TIMB/DBI_Talk5_2001/sld059.htm
===
To: "Tim Bunce" <Tim.Bunce@pobox.com>,
From: "Perrin Harkins" <perrin@elem.com>
Subject: Re: Just while we are so nicely [OT]: SQL Search
Results in pages
Date: Thu, 2 Aug 2001 10:46:05 -0400
> > Is there a generic soloution to this? Has anybody found this worth
writing
> > a pattern?
>
> This may be of interest:
>
> http://search.cpan.org/doc/TIMB/DBI_Talk5_2001/sld059.htm
I've had great success with "Select and cache only the row keys, fetch full
rows as needed". We were also caching the individual records (in
BerkeleyDB), so some pages never needed to hit Oracle at all after the
initial query. A good way to go, if your data is not too volatile.
===
To: Joachim Zobel <nc-zobeljo@netcologne.de>,
modperl@apache.org
From: Robert Landrum <rlandrum@capitoladvantage.com>
Subject: Re: Just while we are so nicely [OT]: SQL Search
Results in pages
Date: Thu, 2 Aug 2001 13:42:57 -0400
At 1:07 PM +0200 8/2/01, Joachim Zobel wrote:
>Hi.
>
>One of the really nice features of MySQL (OK, its not a "real"
>RDBMS, but who cares:) is LIMIT.
>
>Using Oracle I have found it a real pain to display search results
>in pages (of eg 20) while using connection pooling. The problem is
>that you can not be shure to repeat the same resultset on every
>page. This gets a real problem if the queries get expensive and I
>can't afford sorting because it destroys first row performance.
You can with a result index.
When your user first gets to the page, dump the rowids and rownums to
a result_index table.
create table result_index (
session_id number(10),
myrowid varchar(30),
myrownum number(10),
mydate date
);
insert into result_index select 123456,rowid,rownum,sysdate from your_table;
The date column is so that you can purge the result index after 24 hours...
delete from result_index where mydate < sysdate - 1;
Then, for the query...
select * from
result_index a,
your_table b
where
a.session_id = ? and
a.myrowid = b.rowid and
a.myrownum between ? and ?
I have not actually implemented this anywhere. I wrote something
similar where I limited based on rownum, but rownum only numbers the
result set, not the table set.
===
To: "Tim Bunce" <Tim.Bunce@pobox.com>,
From: "raptor" <raptor@unacs.bg>
Subject: Re: Just while we are so nicely [OT]: SQL Search
Results in pages
Date: Thu, 2 Aug 2001 19:10:49 +0300
> This may be of interest:
>
> http://search.cpan.org/doc/TIMB/DBI_Talk5_2001/sld059.htm
]- Where is $h->{FetchHashKeyName}, I didn't found it even in the source
perl -m DBI.... my version is 1.18
===
To: Perrin Harkins <perrin@elem.com>
From: Dave Rolsky <autarch@urth.org>
Subject: Re: Just while we are so nicely [OT]: SQL Search
Results in pages
Date: Thu, 2 Aug 2001 13:54:13 -0500 (CDT)
On Thu, 2 Aug 2001, Perrin Harkins wrote:
> I've had great success with "Select and cache only the row keys, fetch full
> rows as needed". We were also caching the individual records (in
> BerkeleyDB), so some pages never needed to hit Oracle at all after the
> initial query. A good way to go, if your data is not too volatile.
This is more or less how Alzabo works (but you can tweak it). When it
fetches row objects, its really just fetching primary keys that match your
query. As you request other columns they are fetched and cached. You can
also specify some columns to be fetched with the primary key and specify
that others should be considered a group (when A is fetched, get B & C
too).
Actually, I stole a lot of this from Michael Schwern's Class::DBI and got
some more ideas from your talk at ApacheCon, Perrin.
So if people are interested in implementing this, they may want to just
consider using Alazbo (or you can rip out the caching code and use that
separately if you want).
===
To: Robert Landrum <rlandrum@capitoladvantage.com>
From: Joachim Zobel <nc-zobeljo@netcologne.de>
Subject: Re: Just while we are so nicely [OT]: SQL Search
Results in
Date: Thu, 02 Aug 2001 22:54:21 +0200
At 13:42 02.08.01 -0400, you wrote:
>At 1:07 PM +0200 8/2/01, Joachim Zobel wrote:
>>Hi.
>>
>>One of the really nice features of MySQL (OK, its not a "real" RDBMS, but
>>who cares:) is LIMIT.
>>
>>Using Oracle I have found it a real pain to display search results in
>>pages (of eg 20) while using connection pooling. The problem is that you
>>can not be shure to repeat the same resultset on every page. This gets a
>>real problem if the queries get expensive and I can't afford sorting
>>because it destroys first row performance.
>
>You can with a result index.
>
>When your user first gets to the page, dump the rowids and rownums to a
>result_index table.
>
>
>create table result_index (
> session_id number(10),
> myrowid varchar(30),
> myrownum number(10),
> mydate date
>);
>
>insert into result_index select 123456,rowid,rownum,sysdate from your_table;
The problem with this is that it also destroys first row behaviour. When
the insert has started running i would like to display the first rows.
I am thinking about a solution that only inserts the rows the user has seen
and repeats the select (excluding these rows) if he wants to see others. I
just havent found an elegant PL/SQL way to implement this.
===
To: "Joachim Zobel" <nc-zobeljo@netcologne.de>
From: "Rob Bloodgood" <robb@empire2.com>
Subject: RE: Just while we are so nicely [OT]: SQL Search
Results in pages
Date: Thu, 2 Aug 2001 14:06:55 -0700
> >create table result_index (
> > session_id number(10),
> > myrowid varchar(30),
> > myrownum number(10),
> > mydate date
> >);
> >
> >insert into result_index select 123456,rowid,rownum,sysdate from
> your_table;
>
> The problem with this is that it also destroys first row behaviour. When
> the insert has started running i would like to display the first rows.
>
> I am thinking about a solution that only inserts the rows the
> user has seen
> and repeats the select (excluding these rows) if he wants to see
> others. I
> just havent found an elegant PL/SQL way to implement this.
select blah
from foo
where bar=baz
and rowid NOT IN (
select myrowid
from result_index
where session_id = ?
)
would be how to exclude rows already seen.
===
To: raptor <raptor@unacs.bg>
From: Tim Bunce <Tim.Bunce@pobox.com>
Subject: Re: Just while we are so nicely [OT]: SQL Search
Results in pages
Date: Fri, 3 Aug 2001 10:47:25 +0100
On Thu, Aug 02, 2001 at 07:10:49PM +0300, raptor wrote:
> > This may be of interest:
> >
> > http://search.cpan.org/doc/TIMB/DBI_Talk5_2001/sld059.htm
>
> ]- Where is $h->{FetchHashKeyName}, I didn't found it even in the source
> perl -m DBI.... my version is 1.18
Umm, let's see... that talk was given just a couple of weeks ago,
the title of the slide that refers to FetchHashKeyName is "What's
Planned", and you're using a version released two months ago...
As it happens, I made of release of the DBI just before the conference
and that release, 1.19, does include $h->{FetchHashKeyName}.
===