[Zope-DB] Typical size of result sets

[Dieter Maurer]

M.-A. Lemburg wrote at 2004-12-29 17:14 +0100:
> ...
>> A "max_rows" value of "0" (which is senseless taken verbatim)
>> is intended to get the complete set.
>
>What a poor design... instead of using 1000 as default, Z SQL
>Methods should have used "no limit" as default.
>
>Is this "feature" documented somewhere ?
>
>BTW, we've had complaints from users who set max_rows to 0
>and expected to get back an empty result set. A valid
>expectation if you ask me.

Really?

What purpose should a Z SQL Method have which specifies
that it would like to retrieve at most 0 records?


It is more often the case that a senseless value is used
to code something special. E.g. a session timeout of "0"
means no timeout at all.

Practicality overpowers purity in such cases...

>We are now considering using a None value to serve as meaning
>"not set" - much like Python is doing in a lot of methods and
>functions. A value of 0 will then indeed result in an empty result
>set.

The "max_rows" field currently has an ":int" specifier.
It does not support "integer or None".

Almost surely, the wish to avoid explicit type checking (rather
than using the ":int" specifier) or an additional control
(e.g. a "no limit" checkbox) was the reason to use "0"
as indication for "no limit".

Instead of using "None", it would probably add "(0 means no limit)"
to the "Maximum rows to retrieve" label -- would I implement
my own Z SQL Methods...


If you really have the feeling that the specification
"retrieve 0 records" has a significant use case, you could
use a negative number as "no limit".


However, whether or not you use "None" or "negative number"
to indicate "no limit", you must be aware that Z SQL Methods
modified in this way will probably only work with your DA but
not with other DAs (that work with the current coding).

> ...
>> All DA I know (DCOracle, ZPsycopgDA, ZMySQLDA) use "limit"
>> to implement the "max_rows" feature.
> ...
>BTW, I wonder how the above DAs use LIMIT when fetching data
>from stored procedures

Ups: I checked "ZPsycopgDA" and detected that it does not
use "limit" but handles "max_rows" in a "fetchmany".

I do not have "ZOracleDA" and "ZMySQLDA" at hand.
Maybe, I am wrong for them, too.

My memory says that "limit" is used only for "select" commands.
My memory has nothing for things that are not "select" commands.

>or when having to deal with multiple
>result sets where the DA does not have access to the SELECT
>statement generating the result set.

The DA's I know split a complex command sequence into
a sequence of simple commands.
However, you are right that this splitting need not
result in elementary "select" statements that can be extended
with a "limit" clause.

Moreover, the select statement may already have its own "limit".
In this case, an additional "limit" results in a syntax error.

-- 
Dieter