SQL Server 2008 R2, SQL TABLE, When multiple users update the same field in the same row at the same time?

I have web based email system I have coded in ASP.NET 4.5 C# primarily coded using Javascript and Jquery with a SQL SERVER 2008 R2 backend.

The problem is we let each user of that system have their own read and unread email flags.

We first approached this problem by creating a tblMessageReadByUser table which contained a user record for each and every email that came in. Which you can guess can grow really fast.

Our second approach was to create a separate tblMessageState table, one record for every email. Which contains a string of 1024 1's and 0's and based on user id each user "byte" would be marked as 1 or 0. (we originally thought byte array field but there is a 32 limit and there could be more users than that.

But even this solution isn't 100 percent solid, there is funkiness when the different users read or unread the same email updating the same field at the same time.

Any thoughts or suggestions on the optimal way to handle this scenario? We are upgrading to SQL Server 2014 next year if helps.
Paul MaurielloSoftware Programmer Developer Analyst EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you should stay the "relational" way, as it will not get you into such issues as you describe in the messagestate method.
you may consider a the opposite method, which would say with your additional table the list of users not having read the mail already, which makes the number of rows will rather decrement instead of increment per reading ... in the end, every message gets read, and finding for a user the list of his unread emails is simply a join instead of a not exists...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Paul MaurielloSoftware Programmer Developer Analyst EngineerAuthor Commented:
That's a thought, but know that the user has the ability to mark the email as read and unread at will.

If we stay the relational route, Any thoughts on how to manage the exponentially large  growing tblMessageReadByUser table in terms of size and performance? Emails come in quite often. If either way...we create a tblMessageReadByUser table , say that creates a record everytime the user reads an email or vice versa, its still going to be a large table.

We also do not do hard deletes in our system. We have IsActive =1 Is Delete =0  scenario once a record is created. Or for this particular table should we abandon that for this particular tblMessageReadByUser   table and scenario?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>That's a thought, but know that the user has the ability to mark the email as read and unread at will.
in which case you can add back the row again, not a problem. still, at the end of the day, most messages will be marked as read.

>Any thoughts on how to manage the exponentially large  growing tblMessageReadByUser table in terms of size and performance?
no other way that to have enough storage for the db. it's to note that the size of the information that you need to store who has read/not read a message is small compared to the messages themselves...

>We also do not do hard deletes in our system. We have IsActive =1 Is Delete =0  scenario once a record is created.
this is perfectly possible for the main entities, but for such "unimportant" links, this should not be done.
performance is the main issue here
0
Scott PletcherSenior DBACommented:
I agree, a separate table with a row per email reader, i.e. the standard relational design, is best.  

>> If we stay the relational route, Any thoughts on how to manage the exponentially large  growing tblMessageReadByUser table in terms of size and performance? <<

Yes:
(1) Cluster the table by ( email_id, user_id ) not by an identity column.
(2) Another option, which I'd strongly urge considering, is to insert every user into this table at the time the email is initially processed.  Naturally the flags would all start as "non-read".  As its read status changes, it's only an UPDATE, not an INSERT / DELETE.  This greatly reduces the fragmentation in the table and the logging overhead.
(3) If you don't pre-insert all email uers, I'd partition this table, not primarily because of its size even but because you'll need a relatively very low fillfactor on the current portion of the table that you won't want for more historical partitions.  Far fewer read/not-read changes will occur on older emails.


>> We also do not do hard deletes in our system. We have IsActive =1 Is Delete =0  scenario once a record is created. <<

That's a very bad approach for relational tables in general, and for the email msgs read table in particular.  You're forcing table scans far more often than you realize just because of those flags, which are almost never really needed anyway.  Admit it: virtually every query says "WHERE deleted = 0".  DBAs like me hate how ingrained this in.  [Btw, this is a holdover from tape and other sequential master files, when every record had to be read anyway, so the delete flag never added I/O.]

Instead, create an archive table with the exact same structure as the main table.  Any query that genuinely does need to see deleted rows -- and that's usually an extraordinarily tiny % of queries -- can UNION ALL the two tables.
0
Paul MaurielloSoftware Programmer Developer Analyst EngineerAuthor Commented:
Thanks guys I appreciate the input big time!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.