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
340 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:pmauriello
  • 2
  • 2
5 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 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:pmauriello
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 142

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:ScottPletcher
ScottPletcher earned 250 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:pmauriello
ID: 40443734
Thanks guys I appreciate the input big time!
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

705 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now