?
Solved

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

Posted on 2014-11-14
5
Medium Priority
?
363 Views
Last Modified: 2014-11-14
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.
0
Comment
Question by:Paul Mauriello
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 40442426
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
 

Author Comment

by:Paul Mauriello
ID: 40442445
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40442508
>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
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 total points
ID: 40443104
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
 

Author Comment

by:Paul Mauriello
ID: 40443734
Thanks guys I appreciate the input big time!
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question