lou wrote:
In some email I received from Gianni Mariani <[EMAIL PROTECTED]> on Wed, 02 Jul
2003
00:00:16 -0700, wrote:
..
and how exactly using transaction will make it
faster, please elaborate?
Try a test. Doing a bunch of updates in a single transaction is faster
than doing multiple transactions.
okay, since PgSQL will wrap a single query inside a transaction in general,
<lock tables;>
start trans;
insert_message(id,blahblah);
insert_messageblks(id,blahblah);
commit;
<unlock tables;>
so this things limits to one transaction,
rather than
insert_message(id,...);
insert_messageblks(id,..);
which is going to perform 2 transactions.
actually I havent looked too deep in the recent 7.3 PgSQL branch to find out
new performance goodies, but i will soon.
There is actually no <lock tables> semantics in pgsql so there is no
contention between queries. This is probably not a big deal for dbmail
but it can be for other applications.
I suppose rewriting the queries might speed up things a little
bit. it's quite possible to wrap some queries into transactions, but I cant see
_this_
really good reason why this should be implemented at this point (safe model).
Transactions are safer. It means that if you do multiple updates,
you're only going to commit completed ones.
Obviously, 'everything or nothing' approach rock ;)
b) dbmail could also use stored procedures in Postgresql. This would
eliminate a bunch of processing on the server.
[...]
why? it will break portability with other servers like mysql. Also that means
all the
load to be pushed to the database server rather than the client (dbmail server).
(MySQL have stored procedures in 4.0.x or?)
Because pgsql procedures automagically prepare statements and hence
there is a lot less "planning" going on.
yes, but you can make pgsql analyze queries :)
everytime a query is parsed it is then "planned". So it does analyze.
Also, you really don't want the C code to know too much about the
schema. (Encapsulation peeve on mine).
yes, but can you make it learn, or write it so generically.
This is imap/mail we're talking about. It's a done deal. You have a
dozen or 2 imap commands. Implement each one as a stored procedure (or
set of procedures). Simple 1.Parse imap message 2.call stored procedure
3,return result. Have a generic mapping from command to stored
procedure and you can probably write all the stored procedures in an
afternoon. Changes to the schema and procedures means you won't even
need to re-start the imap server ....
I'm trying to find the balance between pgsql/mysql db APIs in dbmail
and basically merge them in one.. hope to find some time in the next few
months;)
It will be a while before mysql gets there. Contrary to some opinions,
Postgresql has an extreme number of features and is rock solid compared
to mysql. I think it's a mistake to go for the least common denominator
of Postgresql and mysql. Postgresql 7.4 has a whole bunch of new ones
This is a very simple database so I suggest to create a (C++) interface
for each operation you want to do (imap command or otherwise) and have
an implementation of a processor that does each kind of message.
Anyhow, that's my $0.02 worth....
Regards
G
