Subject Either update conflict or count slow - Cant implement mail functionality
Author GOVINDKRISHNA
Hello list,

I need to implement the following functionality.

There is an appserver downloading mail and storing it in a database. It should keep track of how many mails have been downloaded.
When the client connects to the database I want to show one of those popups saying "you have :N new mails". After that if the user logs in, it should not show this popup. If the client is running and a new mail is downloaded it should again pop up a message

Tried creating a record and updating it each time a mail was downloaded. The client used to check on startup and a timer tick. If there was a number it would popup and reset it to 0. This lead to some pesky random update conflicts which was traced down to this.

So lurking on this list I read some mails which was talking about keeping track of stock etc. And so I switched to inserting a record for each downloaded mail and then do a count - display and delete those records so the update conflict problem went away. But count is becoming very slow. (Many Many users mail are stored in the same database so the count has a where condition on userid)

Also we need to find out number of mails in a particular folder etc. Count is dead slow.

So What is the best way to do something like this in Firebird.


The data structure is like this.
I have table tblMessages which has all message. A tblMessageFolderRelation which has MessageID and FolderID.
I want to display number of message in each folder. Best way to do this.


gk



>>> This mail is created in Hamsa (www.jlmatrix.com) <<<