Re: Just while we are so nicely [OT]: SQL Search Results in pages
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_idnumber(10), myrowid varchar(30), myrownum number(10), mydatedate ); 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. Rob >Is there a generic soloution to this? Has anybody found this worth >writing a pattern? > >Thanx, >Joachim >-- >"... ein Geschlecht erfinderischer Zwerge, die fuer alles gemietet werden >koennen."- Bertolt Brecht - Leben des Galilei -- "A good magician never reveals his secret; the unbelievable trick becomes simple and obvious once it is explained. So too with UNIX."
Re: Just while we are so nicely [OT]: SQL Search Results in pages
On Thursday, August 2, 2001, at 04:07 AM, Joachim Zobel wrote: > 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? Sure! Just store the results in your session, and display from there... -- -- Tom Mornini -- ICQ 113526784
Re: Just while we are so nicely [OT]: SQL Search Results in pages
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}.
Tim.
Re: Just while we are so nicely [OT]: SQL Search Results in pages
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). -dave /*== www.urth.org We await the New Sun ==*/
RE: Just while we are so nicely [OT]: SQL Search Results in pages
> >create table result_index ( > > session_idnumber(10), > > myrowid varchar(30), > > myrownum number(10), > > mydatedate > >); > > > >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.
Re: Just while we are so nicely [OT]: SQL Search Results in pages
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_idnumber(10), > myrowid varchar(30), > myrownum number(10), > mydatedate >); > >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. Thanx, Joachim -- "... ein Geschlecht erfinderischer Zwerge, die fuer alles gemietet werden koennen."- Bertolt Brecht - Leben des Galilei
Re: Just while we are so nicely [OT]: SQL Search Results in pages
> 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
> Tim.
