[Zope] Z-SQL Method performance

[Rudi Wurm]

my situation:
Zope 2.4.2 (binary release, python 2.1, linux2-x86)
ZMySQLda 2.0.8, MySQLdb Revision 1.11

My problem is the perfomance when executing a Z SQL Method
selecting a huge number of records, like 'select nr, name, ... from <table> 
where ...'. dependend on the where-specification i have to deal with sets 
about =~ 10000.
my mysql-client tells me, that mysql by itself takes about .02 to .04 secs to 
perform
the query, when enterd by hand into the mysql-client.
a small python-program using routines from the _mysql-module doesnt take much 
longer.
But telling a dtml-method to use the Z-SQL-Method in an <dtml-in <name of 
z-sql-method>> statement, it takes about 4-5 seconds, and thats very long.
Does one know how to speed it up or how to tune a z-sql-method, and whats the 
reason for the delay ?
And: when i test a query in the Z SQL Method test-tab, i get 50 results per
page, with the same speed-delay. but when i click on the <next 50 ...> link, i
get instantly the next 50 records displayed. How is this perfomed and how can
i achieve this in batch-processing via <dtml-in start=?? ...> ? The 
batch-approach i
use looks like

<dtml-in expr="sql-query(REQUEST)" size=10 start=query_start>
 <dtml-if sequence-start>
  <dtml-if previous-sequence>
   <a href="<dtml-var URL><dtml-var sequence-query>query_start=<dtml-var      
                     previous-sequence-start-number>"</a>
  </dtml-if>
 </dtml-if>

  (my display of data)

 <dtml-if sequence-end>
  <dtml-if next-sequence>
   <a href="<dtml-var URL><dtml-var sequence-query
             >query_start=<dtml-var next-sequence-start-number>"></a>
  </dtml-if>
 </dtml-if>
</dtml-in>

, its like the example from the zope-book, batch processing with in-tag, 
chapter 8.

activating caching for the Z SQL method helps not, because when i use it it 
returns
allways the old set of data although the variables specifying the 
where-clause have changed, when a new query is send.

thanks in advance

 Rudi

free people, free living, free software