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 =~ 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 =~ 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