[Zope-DB] Zope MysQL Slowwwwwwwwwwwww on Windows

[working4aliving]

Check the advanced tab on you zsql methods to limit the maximum rows to
retrieve.
If you want to run mysql on your windows machine, you can run zope from your
"generic" python (c:\python21)
that HAS the mysqllib installed.  You wouldn't need odbc then.

Bob


----- Original Message -----
From: Deb B.
To: zope-db@zope.org
Sent: Tuesday, December 24, 2002 2:54 PM
Subject: [Zope-DB] Zope and MySQL Slowwwwwwwwwwwww on Windows


I came to this group today to see what I could find on a particular
ZOPE/MySQL issue I'm having. It relates to this thread, somewhat. I
hope someone can give me some insight.

I have ZOPE installed on a WIN98 machine that is acting as a
temporary server until we go through a complete network upgrade to
Windows 2000 Server, et. al. I have a development workstation that is
using Windows 2000 Professional. This machine is where I have
installed MySQL. So, I have ZOPE on one machine and MySQL on the
other.

I installed the myODBC from mysql.com because I couldn't get any of
the ZMySQLDA/MySQLdb products to work on any version of Windows
(obviously it favors the Unix/Linux environment). Well, Windows is
what I'm stuck with now.

I created databases, set up my ODBC connections, set up my Z SQL
Methods and SQL Forms (depending upon which databases I access) and
can access the databases with beautiful results on my ZOPE
pages......BUT.....the results are extremellllllllllllllllllllly slow
in coming. Even small databases with just a few records take forever
to come up even with the most basic of SQL statements using either
SQL Forms (for example, my Purchase Order database):

<dtml-var standard_html_header>
<dtml-if CheckPermissions>

<dtml-unless REFERER><dtml-call "REQUEST.set
('REFERER',HTTP_REFERER)"></dtml-unless>
<dtml-unless sqf_edit_method><dtml-call "REQUEST.set
('sqf_edit_method','edit')"></dtml-unless>

<h3>> <dtml-var title_or_id>: <dtml-var document_title> (List)</h3>
<P>
<a href="&dtml-URL2;/">Up</a> -
<a href="&dtml-REFERER;">Back</a> -
<a href="list?REFERER=&dtml-REFERER;">List</a> -
<a href="add?REFERER=&dtml-REFERER;">Add</a>

<br><br>


<table CELLSPACING='&dtml-table_cell_spacing;'>
<tr BGCOLOR='&dtml-internal_title_color;'>
<th>P.O.#</th>
<th>Order Date</th>
<th>P.O. Vendor</th>
<th>Items</th>
<th>Terms</th>
<th>Total</th>
</tr>


<dtml-call "REQUEST.set('bat_size',_.int(viewlistlen))">
<dtml-unless start_key><dtml-call "REQUEST.set('start_key',0)"></dtml-
unless>
<dtml-if "_.int(start_key)<0"><dtml-call "REQUEST.set
('start_key',0)"></dtml-if>
<dtml-call "REQUEST.set('next_key',_.int(start_key))">

<dtml-call "REQUEST.set('flag',0)">

<dtml-call "REQUEST.set('q','select * from po2002 where autoid
>= '+_.str(start_key)+' order by autoid asc')">


<dtml-try>
<dtml-in "SQL(q=q)" size=bat_size>
<dtml-if "_.int(next_key) < _.int(autoid)">
  <dtml-call "REQUEST.set('next_key',autoid)">
</dtml-if>

<tr valign="top" align="left" BGCOLOR='<dtml-if "_.int(flag)
==1">&dtml-internal_c1_color;<dtml-else>&dtml-
internal_c2_color;</dtml-if>'>

<td><a href="&dtml-sqf_edit_method;?edit_key=<dtml-var
autoid>&REFERER=&dtml-REFERER;"><dtml-var autoid></a></td>
<td><dtml-var order_date></td>
<td><dtml-var vendor_name></td>
<td><dtml-var items></td>
<td><dtml-var terms></td>
<td><dtml-var total></td>
</tr>
<dtml-call "REQUEST.set('flag',1-_.int(flag))">
</dtml-in>

<dtml-except>
  <h1>General error</h1>
  <dtml-var errorinternalmethod>
</dtml-try>

</table>
<!-- navigation links -->
<br>
<a href="&dtml-URL0;?start_key=0&REFERER=&dtml-REFERER;">First</a> -
<a href="&dtml-URL0;?start_key=<dtml-var "_.int(start_key)-_.int
(bat_size)">&REFERER=&dtml-REFERER;">Prev</a> -
<a href="&dtml-URL0;?start_key=<dtml-var "_.int
(next_key)">&REFERER=&dtml-REFERER;">Next</a>

<dtml-else>
    <dtml-var standard_html_header>
</dtml-if>
<dtml-var standard_html_footer>

and then Z SQL Methods (for example, a document log database):

Example 1:

select * from yr2000

Example 2:

SELECT * FROM yr2000 WHERE doc_author='JEF'

I also tried to set up a search form so users can perform searches
themselves instead of having to be limited to choosing from pre-
constructed reports I provide them. It's
daaaaaaaaaaaaaaawwwwwwwwwwwwwwwggggggggggggggg slowwwwwwwwwwwwwwwww
though. I even went so far as to perform table structure tests on my
MySQL databases to ensure I was optimizing them for the fastest
performance. Any searches done through a MySQL client returns quick
results but I want all of their access to these databases to be web-
based (i.e., through ZOPE site). Help!

Thanks.
Deb






Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now