Forum OpenACS Q&A: Re: Bugtracker Search Live on OpenACS.org

Collapse
6: Re: Bugtracker Search Live on OpenACS.org (response to 1)
Posted by Dave Bauer on 02/03/06 05:16 PM
Well I restarted AOLserver (it was at 718mb) and vacuumed the database. it didn't help. Main issue is the existing code runs the search query twice, one to search and once to count the results.

We need to write a "best guess" result estimator, sort of how Google gives results. Its faster, but still not efficient to do the count seperately on Oracle.

Collapse
10: Re: Re: Bugtracker Search Live on OpenACS.org (response to 6)
Posted by Brian Fenton on 02/13/06 01:36 PM
Heh, this is interesting. I recently had the opportunity to hear Tom Kyte (from asktom.oracle.com) talk about this. He demonstrated how Google completely guesses the number of results in a search (and also how it slows pretty badly after the first 5 or so pages). He said to never give an exact count, just make a guess using cardinality from v$sql_plan. Or if you're using Intermedia it can give you a guess. He also said never to give a link to a "Last Page" - just give the user an option to sort the query in a different way. I think this is because Oracle is usually tuned to quickly get "First Rows" rather than "All Rows".

Some information here (search on the page for "The application we are developing should use pagination to show the results" and read Tom's answer):
http://asktom.oracle.com/pls/ask/f?p=4950:8:1838703355396606297::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:127412348064

Brian