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

Reply via email to