[Zope-DB] Typical size of result sets

M.-A. Lemburg mal at egenix.com
Tue Dec 21 05:32:31 EST 2004

Hi everybody,

we're currently trying to work around the problems with the
default chosen for max_rows in Z SQL Methods.

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.

Now many users want to get the complete set and thus set the
max_rows limit to a very high value. This, however, can result
in a different problem: if the DA pre-allocates the result set
list depending on the max_rows value, then the user will see
either a MemoryError or poor performance (Python having to
allocate and deallocate huge lists for each and every query).

In the upcoming mxODBC Zope DA 1.0.9 we'll provide two work-arounds
for this problem:

1. An option to ignore the max_rows value altogether:

    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.

2. Fetching the result in chunks of a certain size.

    We will be using 10000 as the default here.

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 ?

Marc-Andre Lemburg

Professional Python Services directly from the Source  (#1, Dec 21 2004)
 >>> Python/Zope Consulting and Support ...        http://www.egenix.com/
 >>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
2004-12-06: Released eGenix mx Extensions for Python 2.4

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

More information about the Zope-DB mailing list