Paul,
With my apologies for not having brought it up earlier... I should really have offered, myself, to do Stored Procedures for you some time ago. Especially given our relationship, and the hard work you're doing, this is really something I could/should have offered you. I was disheartened to realize that another contributor had begun this. I hope I can be forgiven for forgetting - momentarily - that you are offering 'open source' software, and would probably welcome the contributions! It's a major topic I forward with our client base: 'Use the server-side features of PostgreSQL'! Stored Procedures is only one of these features, though the one which would probably have most impact for an app such as DBMail, which repetitively calls queries of roughly the same pattern. The performance enhancements can be _immense_. One good approach, architecturally, is to prepare _all_ recurring procedures within a session at the initiation of the session. Statements prepared in this way make use of the PG server's statistics engine and optimizer; they actually get faster and faster! Of course, this presumes the server is configured to make this all work. I'll be in touch with you via our other channels. Meanwhile, please feel free call on me to help in any way I can. Regards, Lou Picciano ----- Original Message ----- From: "Paul J Stevens" <p...@nfg.nl> To: "DBMAIL Developers Mailinglist" <dbmail-dev@dbmail.org> Sent: Friday, January 7, 2011 4:29:18 AM Subject: [Dbmail-dev] use of stored procedures in postgresql (and mysql?) Hi all, Below change, in a separate branch off the dbmail_2_2 branch (for now) adds support for using a stored procedure on header searches. The author claims massive performance increases: "it took most header search operations down from 20-600 (or more) seconds to 8-1000 ms." The stored procedure was written for postgresql, and I've tested it on PG8.4. Activation of this code requires a compile-time define: env="-DUSE_STORED_PROCEDURES" ./configure --with-pgsql ... You will also need to copy the relevant lines (starting with CREATE LANGUAGE...) from sql/postgresql/create_tables.pgsql and feed them to psql. Obviously I can't merge this with the dbmail_2_2 trees yet (if ever), but I wanted to invite you to review and test this approach. If you're on mysql you'll need to port the stored procedure (and post your patch). For sqlite, alas, no stored procedures - which means this can never be default behaviour - unless we drop sqlite. Finally, I'm surprised by the increased performance. Do we really need stored procedures for increased performance, or can we modify the queries used for better throughput? Your input is most welcome. On 2011-01-07 10:08, Paul J Stevens wrote: > - Log ----------------------------------------------------------------- > commit 7dfa0d9b1544b6930d25ad8a2eaac38bb39a8104 > Author: Paul J Stevens <p...@nfg.nl> > Date: Fri Jan 7 10:04:33 2011 +0100 > > compile-time activation of stored procedures > > commit 719bf0f19a03f726c42103fc64c41c3467049e2c > Author: Jason Chu <j...@xentac.net> > Date: Thu Jan 6 11:01:13 2011 -0800 > > [PATCH] Create and use a more efficient stored procedure for header searching > in Postgresql > > ----------------------------------------------------------------------- -- ________________________________________________________________ Paul Stevens paul at nfg.nl NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31 The Netherlands________________________________http://www.nfg.nl _______________________________________________ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
_______________________________________________ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev