Implementation issue with displaying data from seperate tables

Question

question

ingig asked Nov 27, '09

Implementation issue with displaying data from seperate tables

Think about how the main Facebook page is filled with statuses from your friends and comments about those statuses

We now have a reference of what we want, we'll setup the table schema in a simple way

STATUS
statusId
text

COMMENTS
commentId
statusId
text

as you can see each status can have multiple comments,

The question is: What is the best way to retrieve this data from the database?

I see two scenarios where I retrieve the top 50 rows

Scenario 1:

"SELECT TOP 50 * FROM status"

and when going through each datarow do "SELECT * FROM comments WHERE statusId=@statusId"

This will create 51 queries to the database, which I don't like. Need to think about load on the server

Scenario 2:

"SELECT TOP 500 * FROM status OUTER JOIN comments ON status.statusId=comments.statusId"

This is just one query, but now I need to hope that one status doesn't have 500 comments

Preferred Scenario:

I was thinking of something like this:

"SELECT TOP 50 *, (select * from comments where statusId=status.statusId) AS commentTable FROM status"

where commentTable is a table variable that is returned with the results, I know this isn't possible but it's kinda what I'm thinking

This is a asp.net application, so ado.net will be used, and I'm using Sql Server 2008.

Any ideas

t-sql database performance

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2009-11-27T05:20:49Z

Kev Riley answered Nov 27, '09

If I understand correctly, you want the top 50 status with all their comments?

You can do this using a join and an in-line view or derived table. My only concern with your data is that there seems to be no particular ordering on the status, so how can you define the 'top'?

select
  top50status.statusid,
  top50status.text
  comments.commentid,
  comments.text
from
  (
  select top 50 statusid, text from status  --orderby something?????
  ) top50status
left join
  comments on comments.statusid = top50status.statusid

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2009-11-27T02:10:06Z

TimothyAWiseman answered Nov 27, '09

In most cases using a join is the best and most efficient way to retrieve data like that.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2009-11-28T17:25:07Z

ingig 1 answered Nov 28, '09

Brilliant, the answer works perfectly.

The table schema was very simplified, missing things like timestaps, userid and probably something else. I'll probably order by StatusId desc

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield ♦♦ commented · Nov 28, 2009 at 07:35 PM

Please mark Kev's answer as accepted - that way anybody searching in the future with the same question will know that Kev's answer solved your issue.

0 ·

question