X-Original-To: pgsql-interfaces@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 3E4D34761C0 for ; Sun, 13 Apr 2003 21:24:12 -0400 (EDT) Received: from wolff.to (wolff.to [66.93.249.74]) by postgresql.org (Postfix) with SMTP id 4F5C547580B for ; Sun, 13 Apr 2003 21:24:11 -0400 (EDT) Received: (qmail 19353 invoked by uid 500); 14 Apr 2003 01:24:54 -0000 Date: Sun, 13 Apr 2003 20:24:54 -0500 From: Bruno Wolff III To: John McNamara , pgsql-interfaces@postgresql.org Subject: Re: export to excel format Message-ID: <20030414012454.GA19323@wolff.to> Mail-Followup-To: John McNamara , pgsql-interfaces@postgresql.org References: <20030411142918.GA26041@wolff.to> <8cceb2da.0304111405.26ecc247@posting.google.com> <20030414010528.GA19116@wolff.to> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20030414010528.GA19116@wolff.to> User-Agent: Mutt/1.5.4i X-Spam-Status: No, hits=-38.0 required=5.0 tests=BAYES_10,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT, REFERENCES,REPLY_WITH_QUOTES,USER_AGENT_MUTT autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/120 X-Sequence-Number: 4059 On Sun, Apr 13, 2003 at 20:05:28 -0500, Bruno Wolff III wrote: > On Fri, Apr 11, 2003 at 15:05:34 -0700, > John McNamara wrote: > > bruno@wolff.to (Bruno Wolff III) wrote: > > > > > I wrote a perl program that gets data from > > > the database and writes excel spreadsheets. It uses Spreadsheet::WriteExcel > > > which you can get from CPAN. It seems to be pretty slow. It takes about > > > 3 minutes to produce a 4MB spreadsheet. > > > > > > Are you sure that the delay is caused by Spreadsheet::WriteExcel. The > > following program produces a 4MB file in 3 seconds on a P700: > > That runs in a couple of seconds here. There must be something else making > it a lot slower. Splitting the sheets out with ParseExcel takes about a > minute, so just having about four hundred worksheets may account for > a good chunk of the time if there is substantial per sheet overhead. > There also is a query per sheet that takes about a quarter of a second per > sheet. This would account for about a minute and a half right there. I double checked and the query seems to now be taking about .6 seconds for sheets on the bigger end. That would account for virtually all of the time. I had problems with a similar version of this query that slowed down a lot in 7.3.1 or 7.3.2 when an incorrect optimization bug was fixed. Moving to 7.4 development cut the time for at least the original version in half. Looking at the explain results makes it seem there is some hope for improvement as the plan doesn't look like it is taking full advantage of available indexes. But I need to look at it some more to see why that might be. Thanks for pointing out that WriteExcel was not really the problem. I couldn't have done much about that, but I think I have a chance to make the query run significantly faster.