Hi,
Try this:
balance table:
RECID USERID countertype counter
1 90 'TOTALNEW' 12
2 90 'TOTALOLD' 1789
3 90 'NEW' 1
4 90 'NEW' 1
5 90 'OLD' 1
6 90 'OLD' 1
7 90 'NEW' 1

(i prefer to use numeric flags instead of string flags)

here is newmail status:
select sum(counter) as TOTAL_NEW from balance where countertype in
('NEW','TOTALNEW') where userid=90

insert new record with id 8 and userid 90 //
commit;
if(success) start to cleaning operation...

select count('NEW') AS NEWRECS where id <=8
update balance set counter=counter+NEWRECS where
countertype='TOTALNEW' and userid=90;
delete from balance where countertype='NEW' and USERID=90 and id<=8
commit;
if(not success) rollback; // dont wory, give a chance for next time

this technique will keep small your balance table without locks.

Note: you can write cleaning procedure to minimize client coding.

Regards.

Ali


--- In firebird-support@yahoogroups.com, "GOVINDKRISHNA"
<gkrishna@j...> wrote:

> 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) <<<