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?)