[Zope-DB] Typical size of result sets

[Dieter Maurer]

M.-A. Lemburg wrote at 2004-12-21 11:32 +0100:
> ...
>Background: max_rows defaults to 1000 rows for Z SQL Methods,
>so if you happen to have result sets that are larger, you won't
>see more than 20 rows unless you adjust the max_rows value
>in the Z SQL Method.

Really. I did not get 20 rows but whatever value was used for
"max_rows"...

>Now many users want to get the complete set and thus set the
>max_rows limit to a very high value.

A "max_rows" value of "0" (which is senseless taken verbatim)
is intended to get the complete set.

> ...
>1. An option to ignore the max_rows value altogether:

The value "0" activates this option...
At least, this is the intension.

>    The most efficient way of dealing with limited result sets is by
>    using the LIMIT SQL construct which can be dealt with on the
>    server side rather than the client side - the max_rows parameter
>    is simply not needed for todays databases.

All DA I know (DCOracle, ZPsycopgDA, ZMySQLDA) use "limit"
to implement the "max_rows" feature.

>2. Fetching the result in chunks of a certain size.
>
>    We will be using 10000 as the default here.

This sounds not bad -- but it will probably not play well with the
cache feature of Z SQL Methods.

>Question: Given your experience in the field, will 10000 rows be
>a good compromise between gained speed by pre-allocation (such a
>list consumes around 40kB of RAM) and the desire to be able to
>fetch "unlimited" result sets using Z SQL Method ?

Why should the DA preallocate the result at all?
It cannot use the same list for subsequent requests because
the first list may still be in use when the second request
is issued...

-- 
Dieter