In a multi user app I want to give an indication in semi-real time as to how many emails there are and how many haven't been read by a particular user.

There are 4 tables involved:

emCounts: hold the counts of how many emails and the number unread
EMails: guess
MailBoxes: as it says
emReadStatus: an entry consisting of the mailbox number, user id and message number is written to this AFTER the mail has been read

The script below works well (ie delivers the right result) but has one tiny flaw - it takes c1sec to execute and since it will be run lots and lots with the resultant table being used to refresh the display I need it to be faster.



SCRIPT
BEGIN

BEGIN
EXECUTE IMMEDIATE 'DROP TABLE emCounts';
EXCEPTION
END;

EXECUTE IMMEDIATE 'CREATE TABLE emCounts AS
                 SELECT
                    _fkMailBoxes,
                    COUNT(_fkMailBoxes) AS _OnFile,
                    0 AS _Read,
                    0 AS _UnRead,
                    FALSE AS _Monitored
                 FROM
                    NLH.EMails
                 GROUP BY
                    _fkMailBoxes
                 WITH DATA';

EXECUTE IMMEDIATE 'UPDATE emCounts SET _Monitored =
                 (SELECT _Monitored FROM NLH.MailBoxes
                  WHERE _BoxNo = emCounts._fkMailBoxes)';

EXECUTE IMMEDIATE 'UPDATE emCounts SET _Read =
                 (SELECT COUNT(*) FROM NLH.emReadStatus
                  WHERE
                  _fkStaff = ''RL''
                  AND
                  _fkMailBoxes = emCounts._fkMailBoxes)';

EXECUTE IMMEDIATE 'UPDATE emCounts SET _UnRead = _OnFile - _Read WHERE _Monitored';
END

Roy Lambert

Forgot to say the mailbox (_fkMailBoxes & _BoxNo) is and integer and indexed. There are roughly 12k emails currently


Just tested the simple select

SELECT
_fkMailBoxes,
COUNT(*) AS _OnFile,
0 AS _Read,
0 AS _UnRead,
FALSE AS _Monitored
FROM
EMails
GROUP BY
_fkMailBoxes

and even that is c.7secs so I'm not sure there's anything I can do

Roy Lambert

Are all the relevant fields indexed in the relevant tables ?
I have always thought of you as a bit of a whiz with SQL
and that script looks good to me.
Somebody oneday will come up with a killer replacement
for it no doubt.
I'am amazed at how fast that piece of code John Hay
shared with me for counting all the balls in the lottery
since 1994 tho not a huge table or even a big table the
complexity of the count and then throw up the lines with
3 or more is express.
I'am stumped on a piece of SQL right now but its nearly
there so I shall not ask just yet
Rita


"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:72E658F4-1BDC-4F74-A44F-6811C8467907@news.elevatesoft.com...
> Forgot to say the mailbox (_fkMailBoxes & _BoxNo) is and integer and
> indexed. There are roughly 12k emails currently
>
>
> Just tested the simple select
>
> SELECT
> _fkMailBoxes,
> COUNT(*) AS _OnFile,
> 0 AS _Read,
> 0 AS _UnRead,
> FALSE AS _Monitored
> FROM
> EMails
> GROUP BY
> _fkMailBoxes
>
> and even that is c.7secs so I'm not sure there's anything I can do
>
> Roy Lambert
>

Rita


Yup all necessary fields are indexed

Roy Lambert

Dear Roy,

> Yup all necessary fields are indexed

Wait 2.03 ... :-P

--
Charalampos Michael - [Creation Power] - http://www.creationpower.gr

Charalampos


and what will that do?

Roy Lambert

Roy

> In a multi user app I want to give an indication in semi-real time as to
how many emails there are and how many haven't been read by a particular
user.
>
> There are 4 tables involved:
>
> emCounts: hold the counts of how many emails and the number unread
> EMails: guess
> MailBoxes: as it says
> emReadStatus: an entry consisting of the mailbox number, user id and
message number is written to this AFTER the mail has been read
>
> The script below works well (ie delivers the right result) but has one
tiny flaw - it takes c1sec to execute and since it will be run lots and lots
with the resultant table being used to refresh the display I need it to be
faster.

I guess it depends which part of the script is taking the time.  Presumably
it is always going to get slower as the number of emails on file increases.

The "onfile" count looks like it can be maintained as part of the mailboxes
table with an insert/delete trigger on the emails table.

Updating the _read column may improve (or not if you do a SELECT
_fkMailBoxes,COUNT(*) FROM NLH.emReadStatus etc into a temp table and update
from there.


John

John

>The "onfile" count looks like it can be maintained as part of the mailboxes
>table with an insert/delete trigger on the emails table.

That's effectively what I decided to do.

Roy Lambert

Roy

Just for interest I ran a quick test on using "group by" before updates and
the performance stinks compared to the single row select.  In my test the
single row select was so quick that as long as everything is indexed I
suspect the following query should be very fast.

SELECT
_Boxno,
(SELECT COUNT(*) AS _OnFile FROM NLH.EMails WHERE
_fkMailBoxes=MailBoxes._Boxno) AS _Onfile,
(SELECT COUNT(*) FROM NLH.emReadStatus
WHERE
_fkStaff = ''RL''
AND
_fkMailBoxes = MailBoxes._Boxno) AS _Read,
IF(_Monitored THEN
(SELECT COUNT(*) AS _OnFile FROM NLH.EMails WHERE
_fkMailBoxes=MailBoxes._Boxno)-
(SELECT COUNT(*) FROM NLH.emReadStatus
WHERE
_fkStaff = ''RL''
AND
_fkMailBoxes = MailBoxes._Boxno) ELSE 0) AS _UnRead,
_Monitored
FROM
NLH.MailBoxes


John

John


Actually its about half the speed of my original script at c1.8 secs. The solution I'm using now with storing the _OnFile data and keeping it up-to-date (hopefully) with triggers reduces the whole cycle down to c20ms. There was one other tiny problem with your query - it produces the wrong results

The conclusion I came to was that there were only a few mailboxes that anyone would care about there being unread emails in ie those where emails were received into (eg the inbox) the rest of them emails are stuffed into after being viewed / handled. This is what the _Monitored flag in MailBoxes is all about. The mailbox tree display shows the name and either a blank (nothing of file) or -/nn or xx/nn with nn being the number of file, xx being the number unread. A - is used for the unmonitored mailboxes.

The advantage is with say 12k emails, 500 mailboxes and 5 staff I may only have a few dozen entries in my emReadStatus table

Roy Lambert