[Zope] Ri:Z-SQL Method performance

[Stefano Vedovelli]

Hello Rudi

>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 =3D~ 10000.
when a ZSQL method is executed, the reult is fetched into a list object. If your query returns a great amount of records, the process of loading the list can take long. 

>my mysql-client tells me, that mysql by itself takes about .02 to .04 secs to 
>perform
The MySQL client does not fetch all rows at once; rather, it retrieves the correct number of rows depending on your screen.

>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
The fact that the next 50 elements are displayed so fast, is that because either Zope and MySQL perform a little caching. If you wait long enough, you will see that the display will take more.

Let me advice you, however, that having a query that returns 10000 rows in a web application is bad practice or bad design. This is because you can potentially have thousands users requesting the same page, thus running the same query over and over on the database, potentially causing a loss of performance and can create a big swapping activity, especially if the record is big. It is true that sql servers cache and manage results, but this is still not a good excuse to allow a potentially high number of users to run such am heavy query.

You should consider to break it up. And redesign properly. Do you like to flip through thousands of pages to reach the record you want?

:)

Hope this helps
Stefano


Message: 6
From: Rudi Wurm <net_seeker@web.de>
To: zope@zope.org
Date: Tue, 2 Apr 2002 20:15:42 +0200
Subject: [Zope] Z-SQL Method performance

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 =3D~ 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=3D?? ...> ? The 
batch-approach i
use looks like

<dtml-in expr=3D"sql-query(REQUEST)" size=3D10 start=3Dquery_start>
<dtml-if sequence-start>
<dtml-if previous-sequence>
<a href=3D"<dtml-var URL><dtml-var sequence-query>query_start=3D<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=3D"<dtml-var URL><dtml-var sequence-query
>query_start=3D<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