Re: Help needed optimizing display of search results in a web page

"Nicolas MONNET" <nico_at_monnet.to> wrote in message news:slrn8p7g8p.pcj.nico_at_nico.it-xchange.com...
> Hi there,
>
> This is a classic web programming issue. User inputs several
> search criterions (say, part number, manufacturer, price, etc ...),
> programs generates a SELECT query based on those entered
> in web form, and displays it as a Web page.
>
> Now, of course, given that the DB has 1million+ entries, some
> search results might NOT fit in the page, needless to say ..
> so I want to display it as a search engine would do:
>
> [<<] _1_ _2_ _3_ _4_ (5) _6_ _7_ [>>]
>
> To do this, every request does:
>
> SELECT what,i,want FROM table WHERE condition='whathewants'
> AND rownum < $startindex+$linesperpage+1
>
> And then, to get to the start of the page, I fetch $startindex
> rows ...
>
> Problem 1: not very efficient. Lots of time lost fetching useless
> rows.
>
> Problem 2: Oracle spends lots of time collecting data that will
> never be used, if the condition is very broad for instance.
>
> How could I make this more responsive? When I did this approach
> in Mysql it was quick enough (using their 'LIMIT' statement).
> In Oracle however, this yields up to 10 second waits for each page when
> the search is broad.
>
> I was thinking of storing search results in temp tables, so that
> subsequent page views on the same search would be instantaneous,
> but that's alot of work it seems.
>
> Any idea? How is this usually implemented?

Create the temp tables with sequences:

CREATE TABLE user_session||<table_sequence>.nextval as <your SQL statement>
WHERE <fetch_sequence>.currval < rownum < <fetch_sequence>.nextval

The table_sequence can have an alphanumeric increment; The fetch_sequence can increment by however many rows you want in each display;

In your code, all these can be created in the background while the user is viewing the first fetch

You then drop the tables after the user exits

To further the performance you can specify separate tablespaces for these on high-speed drives sized for only what's needed at a given time; make sure PCTINCREASE is >= 1 so PMON will automatically coalesce the temp segments Received on Fri Aug 11 2000 - 00:00:00 CDT