Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2003-10-17 : 13:23:33
|
| Ack!, I just lost my post cuz it took me sooo long to type out. Here we go again!I am trying to add some functionality to my application as follows. Each user has a "Buddy List" that they use to keep their buddy's tagged. It is a simple design as suchUserID, BuddyID100,102100,105100,133105,199105,222222,433When a user looks at another user's info I want them to see how their relationship to them. For instance, if "User 100" looks at "User 222" they see that they are connected thru "User 105".This is the least complex scenario as it only takes 3 levels. I want to go up to 6. For example something like this might happen:User 155 is connected to user 333 thru155 -> 111 -> 324 -> 2345 -> 2352 -> 333.Another related piece of information I want to grab is the count of users in the "buddy network". For instance "user155" is connected to 33,000 people in their "buddy network". (goes 6 levels deep)I'm not exactly sure where to go with this, so any help / ideas / input is greatly appreciated. I want to make sure I get off on the right foot and create a solution that is fast, and scalable.How expensive does something like this look? Currently the only query I am running against this table is something to the effect of"SELECT * FROM BUDDY WHERE USERID = @USERID"I have 180,000 rows in this table, so the buddy networks are going to get pretty big, pretty fast I believe. The queries will be hit often so I am planning on doing some caching at the webserver level to minimize how often they are run.Thanks alot for any assistance,Mike123 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-10-17 : 18:26:43
|
| The problem with this is that you don't have a tree as there are interconnections between the branches i.e. you can't add a level number to the entries so I guess you will have to start with you ID and get all the buddies up to 6 levels.One way of doing this is at http://www.nigelrivett.net/RetrieveTreeHierarchy.html.It's for a tree but if you restrict the loop count to 6 it will work for your situation.There are lots of examples if you do a search.You might want to prepopulate a table with each ID and a string containing all the buddies to 6 levels (or in a normalised form) and maintain it on changes or overnight. That will make the query less resource hungry.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
|
|
Help with stored proc design