Mailing List Archive

>>>>> "TR" == Thomas Raschbacher <lordvan@lordvan.com> writes:

TR> has anyone experience with users with lots of folders, where
TR> retrieving the folder list takes 5-10 seconds (tested with just
TR> a python interactive prompt, so it really only does that)

Unfortunately dbmail was written expecting only dumb sql servers, so it
does only simple queries. Listing the folders or opening one does *lots*
of queries, and the network latency adds up quickly.

About a decade ago now I posted some patches for using a CTE (aka with
clause) to use a single sql query when listing the groups. The patches
were postgres-spefic (what I use, and mysql didn't support CTEs at the
time), and so were ignored by upstream.

At the time they reduced the time it took to list my dbmail imap groups
from aroung 5 minutes to around 5 miliseconds. (Not counting the time
the mua needed to process the imap results.)

My patches for dbmail 2 are in the archives.

A similar change is required for opening a group.

It should take a single sql query to list all (or a subset) of a user's
groups. And it should take a single sql query to enter a group.

After that, only another single query should be needed to grab any given
message's messageblks. The rest of the metadata should have been grabbed
by the enter-group query. Then dbmail would be efficient.

But that only works with sql servers which support enough of ansi sql.
CTEs, in particular, are required for dbmail's schema.

-JimC
--
James Cloos <cloos@jhcloos.com> OpenPGP: 0x997A9F17ED7DAEA6


_______________________________________________
DBmail mailing list
DBmail@dbmail.org
http://lists.nfg.nl/mailman/listinfo/dbmail

Hi again.

I was just wondering if anyone is interested in trying to optimize this
(possibly with Stored Procedures or maybe VIEWs) - unfortunately I am
not really that familiar with the code in question to be able to do much
here myself, but I could definitely help with the (PostgreSQL) DB side
if someone wanted to try tackling this.

Regards,
Thomas R

On 2018-03-16 11:29, Thomas Raschbacher wrote:
> hi.
>
> has anyone experience with users with lots of folders, where
> retrieving the folder list takes 5-10 seconds (tested with just a
> python interactive prompt, so it really only does that)
>
> the table has an index on the name, so the DB query should be
> relatively fast - so it seems the delay is somewhere in dbmail-imapd?
>
> Regards
>
> _______________________________________________
> DBmail mailing list
> DBmail@dbmail.org
> http://lists.nfg.nl/mailman/listinfo/dbmail