Sharing a table row between users in SqlServer Azure

Question

Context: A mobile note taking application that is connected to windows azure mobile services. (Sql Server Azure)

Currently I have 2 tables: Users & Notes.

A user downloads their respective notes by querying the Notes table and asking for all notes that have the userID match their own.
Example:

SELECT * FROM Notes WHERE userID = myID;

But now I want my users to be able to share notes between them, so...

I'm thinking of adding a "SharedList" & "SharedListMember" tables, where each note will have a shared list with their respective sharing members on the SharedListMember child table.

Example:

SELECT DISTINCT n.* FROM Notes n LEFT OUTER JOIN SharedList l ON n.list = l.id INNER JOIN SharedListMember lm ON l.id = lm.list WHERE (lm.memberID = myID OR n.userID = myID)

I have added a LEFT OUTER JOIN because not all tasks will be shared.

I would be adding indexes on Notes.list, SharedList.id (Primary Key) , SharedListMember.memberID, SharedListMember.list

Questions: How much performance impact can I expect with this setup ? Is there a faster way? I currently query about a 1000 notes in less than a second. What would happen if I've got 10 million notes ?

Adam · Accepted Answer · 2014-02-15 13:03:19Z

1

You will likely notice no impact on 10 million notes, with this SQL query.

Your bottlenecks will be bandwidth back to your app, if your notes ever contain attachments and latency with the SQL query call to the database, so cache locally if you can and do Async calls where practical in your application.

This is a case of don't try to over optimize a solution that isn't causing a problem. SQL Azure is highly optimized and I have millions of rows in some of my tables and queries return in less than a second and are far more complicated than the one you have shown above.

answered Feb 15, 2014 at 13:03

Adam

16.2k7 gold badges68 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sharing a table row between users in SqlServer Azure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related