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
351 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 143

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

856 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