CODEWITHSUNDEEP
sqlsql-server-2008
Dave
Dave

Reputation: 2417

Better SQL to pull threads & messages?

I am super rusty on my SQL and I want to make sure I am not writing something that will fail under any type of load.

I have a single Messages table that looks like this:

ID int [PK]
ThreadId nvarchar(32)
Read bit
Archived bit
Timestamp datetime
/*---Other non-relevant columns---*/
Sender_MemberId int [FK]
Receiver_MemberId int [FK]

Similar to Gmail, I want to be able to pull all of the threads and all the messages of that thread based on normal messaging uses (inbox, sent, archived, etc...).

Here's the SQL I'm currently using for the inbox:

SELECT * FROM [Messages]
WHERE [ThreadId] IN 
(
    SELECT [ThreadId]
    FROM [Messages]
    WHERE ([Receiver_MemberId] = @MemberId)
)
ORDER BY [ThreadId] DESC, [Timestamp] DESC

This works as I want, but is there a better way to do this? Or if not, what should indexes should I have?

UPDATE: Per Aaron's good suggestion, I've added paging and reduced the amount of info I am pulling right off the bat.

New SQL:

SELECT DISTINCT m.[ThreadId], m.[Subject], m.[To], m.[From], m.[Timestamp]
FROM 
(   
    SELECT ROW_NUMBER() OVER (ORDER BY MessageId) AS RowNumber , ThreadId
    FROM [Messages]
    WHERE [Sender_MemberId] = @p1
) 
AS t
INNER JOIN [Messages] m ON m.ThreadId = t.ThreadId
WHERE t.RowNumber BETWEEN @Skip + 1 AND @Skip + @Take
ORDER BY [Timestamp] DESC

Upvotes: 0

Views: 93

Answers (1)

Daryl Wenman-Bateson
Daryl Wenman-Bateson

Reputation: 3964

You don't need a sub query, this should do

  

  SELECT Id
       , [ThreadId]
       , read
       , archived
       , timestamp
  FROM   [Messages]
  WHERE  [Receiver_MemberId] = @MemberId
  ORDER BY [ThreadId] DESC, [Timestamp] DESC

it's good practice to name the columns you require, rather than use *

Upvotes: 1

Related Questions