SQL Server Performance Forum – Threads Archive

Single With Union or Double Rows

I have a friend table that defines a users friends.
[friends]
[id]
[userid]
[friend_userid]
[usersortorder]
[friendsortorder] Say I want to get User #2’s friends…I would query like this:
SELECT * FROM (SELECT *, [usersortorder] as order FROM friends WHERE userid = 2 UNION ALL SELECT *, [friendsortorder] as order FROM friends WHERE friend_userid = 2) ORDER BY order ASC; As you can tell, there is a SINGLE row for the relationship. Now my application has very complex logic and I’m wondering whether it would be better to have double rows (each friendship has 2 rows, user to friend and friend to user). Do you think single or double is better? This table will have about 500 million rows all properly index

---

Have you checked the Estimated execution plan for those 2 queries, it is better to investigate from that side. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

---

As Satya mentioned above, you can see the differences in Execution Plan. Try avoiding SELECT * and use SELECT list of column names instead.

---

]]>