Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 18 total |
SQL Guru wanted |
Fri, Aug 7 2009 11:27 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Fri, Aug 7 2009 11:35 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Fri, Aug 7 2009 11:57 AM | Permanent Link |
"Rita" | 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 > |
Fri, Aug 7 2009 1:06 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Rita
Yup all necessary fields are indexed Roy Lambert |
Fri, Aug 7 2009 6:56 PM | Permanent Link |
Charalampos Michael | Dear Roy,
> Yup all necessary fields are indexed Wait 2.03 ... :-P -- Charalampos Michael - [Creation Power] - http://www.creationpower.gr |
Sat, Aug 8 2009 3:42 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Charalampos
and what will that do? Roy Lambert |
Mon, Aug 10 2009 6:00 AM | Permanent Link |
"John Hay" | 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 |
Mon, Aug 10 2009 7:25 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Mon, Aug 10 2009 10:08 AM | Permanent Link |
"John Hay" | 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 |
Mon, Aug 10 2009 10:59 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |