[Zope] Problems with *BIG* SQL queries

[Capesius, Alan]

I've seen the same problem, but was able to workaround by having the SQL
server do the totalling.

If you are doing any calcs in the DTML, try moving them to the SQL as
calculated fields. This will reduce the load on Zope. Zope seems to have a
big problem preparing large calculated results through DTML calculations.
External methods might help.

The Zope server side page build process is probably taking a long time, you
can determine this by adding a <dtml-var "ZopeTime()"> to the start and end
of your page and comparing the times.

The sequence looks like this:
31000 records, three fields
w/Zope server running dual 266mhz and SQL Server running dual 1GHz.

Client request to Zope        .1 sec
Zope request to SQL Server    .1 sec
SQL response to Zope          22 sec
Zope internal                115 sec

Zope response to client        3 sec(600mhz client) - 30 sec (dual 266mhz
client)

If as others have suggested, this is a browser rendering problem, you could
easily determine this by removing all HTML table coding from your page and
dumping straight text to the browser. That way there are no client side
rendering delays. In my experience, you will need to significantly increase
client processor speed to correct this. OR... break your output into
separate tables. This improves browser rendering time. For instance, 31000
records displaying 3 fields without tables renders in 2 seconds, but
requires just under 2 minutes for Zope to prepare the page for the client.

With tables, the client pretty much never responds. (10 minutes+)
However, the Zope response time is similar with or without tables.

Here's an example of how I break the tables down for better rendering. This
increases the Zope calc time (from 2 minutes to 3 minutes in my sample test)
but the browser rendering only takes about 10 seconds:

<dtml-var standard_html_header>
<dtml-var "ZopeTime()">

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

<TABLE>
<dtml-in allorders>
<dtml-call "REQUEST.set('count', count+1)">
<dtml-if "count>100">
</TABLE><TABLE>
<dtml-call "REQUEST.set('count', 0)">
</dtml-if>
<TR><TD><dtml-var itemno></TD><TD><dtml-var desc></TD><TD><dtml-var
datelastmn></TD></TR>
</dtml-in>
</TABLE>
<dtml-var "ZopeTime()">
<dtml-var standard_html_footer>








If you are using ZODBCDA for this and are seeing site delays as result of
long running queries, try ZmxODBC instead. It will allow multithreading.

Alan Capesius, MCSE/NTCIP+20
Technical Support Engineer
Sysmex Corporation of America
capesiusa@sysmex.com

Zope+NT: http://twsite.bizland.com/zopetips.htm



>>>-----Original Message-----
>>>From: Mayers, Philip J [mailto:p.mayers@ic.ac.uk]
>>>Sent: Sunday, March 18, 2001 9:17 AM 
>>>To: 'zope@zope.org'
>>>Subject: [Zope] Problems with *BIG* SQL queries
>>>
>>>And no, I don't want to batch the results up. Any ideas? Is 
>>>it just the dtml
>>>processing that's slowing it down? If so, how can I speed it up
>>>(PythonScript to generate the HTML?)