Help needed optimizing display of search results in a web page

Help needed optimizing display of search results in a web page

Post by Nicolas MONN » Sun, 31 Dec 1899 09:00:00



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?

--
"Pirater" consiste couler des bateaux, tuer et violer;
pas partager des logiciels et de la musique avec ses amis.
Les criminels de la proprit intellectuels sont coupables de:
abus de position *e, entente illgale sur les prix,
infraction au droit de la consommation ... vous voyez de qui
je veux parler?

 
 
 

Help needed optimizing display of search results in a web page

Post by Rognvald Bjarn » Sun, 31 Dec 1899 09:00:00



Quote:> 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

 
 
 

1. limit search result display on web page??

Hi people,

we are currently building an ASP/VBScript web page that searches records in
an Access DB using ODBC.

Problem: the number of hits can be several 100 or more, so we need to use
some "25-at-a-time" mechanism.

Constraints:
1. Using session variables to hold the data would flood our server's memory
(just pointers to files or records could be ok.)
2. the solution should be simpler than implementing a full web crawler

So far we intend to count all hits, then have the browser display a
confirmation dialog with the total number of hits. If accepted, we would
send the data over, displaying another confirmation dialog in the client
browser every 25 records.

Better ideas, pointers, code samples, etc would be GREATLY appreciated!!!
Thanks a lot!!!

*************************
please reply by email
*************************

Regards,
Marc Esser

2. Create Window list

3. Display links to other pages in web search results

4. CLIPPER vs. FOXPRO

5. web search with summarized results/web pages

6. 'No language handler installed'

7. How do I display search results across pages???

8. C Routines ?

9. sql query to display search engine paging results

10. Displaying multiple pages of search results

11. displaying search results in a page with frames

12. Displaying MDX Results on a web page

13. Displaying results on a web page