| Author |
Topic |
|
Swede
Yak Posting Veteran
74 Posts |
 Posted - 2002-01-07 : 09:21:49
|
| I once created a tree view forum which Worked really great, problem being it was not as fast as I would have liked it. It basically consisted of 2 loops. The 1st one handled all top level messages and the 2nd handled all messages following each 1st level message.Although, since I wanted the users username to be constantly updated I used the users user_id in the message database and then collected the username with a relationship towards that user_id from a user database.That procedure is not very fast though. And now I started figuring (I don't have the code for the forum left) that if I created a new one. I would enter the username into the message database instead saving me the hassle of searching in 2 databases to get the same data. My thought is that when the user change their username instead it would update the message database where the user_id is the same...Has anyone tried this and found it to work? Or have any ideas or comments towards this? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-01-07 : 09:27:07
|
| It basically consisted of 2 loops.Do you mean you were looping through the messages? If so that is why it was slow.Having the name in a different database (although I suspect you mean table) won't make much difference as you can just join to it to get the value.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
 |
|
|
Swede
Yak Posting Veteran
74 Posts |
Posted - 2002-01-07 : 09:31:33
|
| Yeah, 2 loops. Had to do it that way I supposed. Otherwise it wouldn't list as I wanted them too. A forum like this one is easy, but a tree view one was a bit more troublesome. :)Sorry about calling them databases, meant of course a table. ;)I haven't really done any hardcore SQL programming, I am more of a problem solver. Could you give me a quickie about joining tables? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-01-07 : 09:39:01
|
| If you want to display as a tree then you should design the database with that in mind.Probably have a sort field with the tree sort fieldSomething likerootID + level1ID + level2ID + ... + thismessageIDThen if you get all the messages you want to display and order by this field they will automatically be in the correct order.(see www.nigelrivett.com for a method of creating this field with a temp table).To get the user nameselect messages.text, users.namefrom messages join users on messages.userID = users.IDorder by ...==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-01-07 : 09:39:52
|
R u looking for Syntax of joins.select columns from table1 tinner join table2 t1 on t1.column=t2.columnit can be a inner,left or right outer join.check for Joins in BOL.Beaten by 50 Secs HTH----------------------------Anything that Doesn't Kills you Makes you StrongerEdited by - Nazim on 01/07/2002 09:41:26 |
|
|
|
Swede
Yak Posting Veteran
74 Posts |
Posted - 2002-01-07 : 09:43:33
|
| Thank you, will fiddle with the joining. Is it much faster? I take it it virtually combine the 2 tables into 1 for faster querying? :)And about the table structure. The tables was actually working good, I had no depth count set into the actual table, in case one message would be deleted it wouldn't affect the tree... all working perfect. The actual depth count was produced with a short mathematical algorith instead, quite effective.Why not try and do the impossible? |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2002-01-07 : 09:47:08
|
| We have some articles and links (http://www.sqlteam.com/SearchResults.asp?SearchTerms=tree).===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-01-07 : 09:49:25
|
| If the tables are indexed properly (e.g. users table has unique index on userID) then the retrieval will be very fast - after all this is the basis of a relational database.The idea is to isolate the presentation layer from the storage layer. The interface is via stored procedures. The stored procedure retrives the data and returns it to the presentation layer. How much processing is performed in the presentation layer is up to you but the fastest systems usually have a database that is suited to the client and all this layer does is to format the output for display.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy.Edited by - nr on 01/07/2002 09:50:15 |
|
|
|
Swede
Yak Posting Veteran
74 Posts |
Posted - 2002-01-07 : 09:50:41
|
| Great thanks :D Will read through them!=====================================Why not try and do the impossible? |
|
|
|
Swede
Yak Posting Veteran
74 Posts |
Posted - 2002-01-07 : 09:55:56
|
| If it would help to see the code I created I just got it back :DI can post the 2 sub's which are the 2 loops.=====================================Why not try and do the impossible? |
|
|
|
Swede
Yak Posting Veteran
74 Posts |
Posted - 2002-01-07 : 10:38:42
|
| Cripes! Seems I have still a massive amount to learn about stored procedures and stuff... Seems like the way I did it was a working way, but very primitive... *sighs**goes away and reads more*=====================================Why not try and do the impossible? |
|
|
|
|
Programming a forum issue