[Zope-DB] Typical size of result sets

[M.-A. Lemburg]

Dieter Maurer wrote:
> 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"...

s/20/1000 of course :-)

>>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.

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.

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.

>>...
>>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.

Well, this is not directly possible for ODBC since the different
SQL dialects use different formats for LIMIT construct (or not
provide it at all).

BTW, I wonder how the above DAs use LIMIT when fetching data
from stored procedures or when having to deal with multiple
result sets where the DA does not have access to the SELECT
statement generating the result set.

>>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.

The Z SQL Methods won't notice this since the batch approach
is implemented in the DA. The size of the batch is only important
because you want to avoid having to round-trip to the server too
often.

>>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 is much faster to pre-allocate and then fill in a list
than to create an empty one and then use append to add the
data.

> It cannot use the same list for subsequent requests because
> the first list may still be in use when the second request
> is issued...

Regards,
-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Software directly from the Source
 >>> Python/Zope Consulting and Support ...        http://www.egenix.com/
 >>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::