[ZODB-Dev] Re: [Zope3-dev] Re: Community opinion about search+filter

[Martijn Faassen]

Hey Jim,

Jim Fulton wrote:

> On Mar 25, 2007, at 3:01 AM, Adam Groszer wrote:
>> MF> I think one of the main limitations of the current catalog (and
>> MF> hurry.query) is efficient support for sorting and batching the query
>> MF> results. The Zope 3 catalog returns all matching results, which 
>> can then
>> MF> be sorted and batched. This will stop being scalable for large
>> MF> collections. A relational database is able to do this internally, 
>> and is
>> MF> potentially able to use optimizations there.
> 
> What evidence to you have to support this assertion?  

I have the strong suspicion that modern relational databases are 
currently better able to scale at queries using LIMIT and ORDER BY than 
the Zope 3 catalog. I cannot back this up as I haven't done 
measurements. Perhaps you have done so?

* Do you estimate the performance of the Zope 3 catalog to be equivalent 
to the performance of a modern relational database system for queries 
that need to sort and batch their results?

* If so, do you think it's just as easy for a developer to accomplish 
such equivalent performance with the Zope 3 catalog as it is with a 
relational database?

I've made a number of assertions:

a) one of the main limitations of the current catalog and hurry.query is 
efficient support for sorting and batching.

b) the Zope 3 catalog returns all matching results, which can then be 
sorted and batched. This will stop being scalable for large collections.

I'll amend b) by saying 'This will stop being scalable for large result 
sets'. I agree that b) as stated above is incorrect as the result set 
might be small, but I intended the amendment.

c) A relational database is able to do sorting and batching (limit 
queries) internally, and is potentially able to use optimizations here.

Which of these assertions are false?

Don't you think relational database system that has support for sorting 
and batching built into its query API can at the very least more easily 
use approaches to reduce sorting cost, by rewriting the query, caching, 
and potentially employing special indexes?

> We did some 
> literature search on this a few years ago and found no special trick to 
> avoid sorting costs.

I am at least cursorily aware of challenges surrounding efficient 
querying and batching. I am not looking for a special trick or magic 
bullet. I'd just like more help in avoiding sorting cost in a typical 
situation where results are displayed in a batched format.

If a catalog query returns 1 million results, which I want to show in 
batches of 10, sorted by some property of the results, I would like to 
reduce the costs. Currently the pattern I (and I imagine others) employ 
is to re-execute the query and then sort these results in memory for 
each batch, for each request.

[you list some approaches to reduce sorting cost]

I would like some system that helps me reduce some of these costs, using 
the approaches you list, or at least some caching somewhere. I would 
imagine a relational database for instance can employ caching of result 
sets, so that if no writes occurred, a second LIMIT query asking for a 
different range will return results a lot faster.

Apparently the catalog does support N-best, you state later in this 
thread. How does one use this support? Can I add it to hurry.query somehow?

Perhaps all this is not the reponsibility of the catalog itself, but a 
system surrounding it. As long as it's obviously there for people to use.

Perhaps however I am seeing problems that aren't there?

Do you think there is no problem and we have parity with relational 
database implementations here?

Do you think the current situation cannot be improved much further?

Do you think any further improvements are not worth the costs?

Regards,

Martijn