| Author |
 Topic  |
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
 Posted - 09 October 2002 : 09:53:24
|
s'ok I wasnt really very clear :)
I was think along the context that Mike talked about regarding the admin side of things where Updating, deleting members etc was not something that happened a lot on the forum (something that can benefit from RI providing you don't get yourself into those update anomalie situations), but then if its only 10% of the querys that will benefit from it then why bother ?. I guess if you were going to go down the path of using RI then you would also want to normalise the database a lot more than it is now, this would probably end up with a lot more complex queries being required to just read a single topic.
|
Kiwihosting.Net - The Forum Hosting Specialists
|
 |
|
|
e3stone
Average Member
USA
885 Posts |
Posted - 09 October 2002 : 17:09:32
|
I, like Ruirib, don't see how RI would make the queries any more complex.
Using the following table example:
CATEGORY
CAT_ID (PK)
CAT_NAME
CAT_MODERATION
CAT_SUBSCRIPTION
CAT_ORDER
FORUM
FORUM_ID (PK)
CAT_ID (FK)
F_SUBJECT
F_DESCRIPTION
F_TOPICS
F_LAST_POST_ID
TOPIC
TOPIC_ID (PK)
FORUM_ID (FK)
TOPIC_AUTHOR (FK)
TOPIC_SUBJECT
TOPIC_REPLY_COUNT
TOPIC_VIEW_COUNT
TOPIC_FIRST_POST_ID
TOPIC_LAST_POST_ID
POST
POST_ID (PK)
TOPIC_ID (FK)
POST_AUTHOR (FK)
POST_DATE
POST_TEXT
In this example I placed the original topic message in POST, but the RI concept still applies to the current design.
default.asp needs the Categories, Forums, and the last post in each forum.
ok, categories come from the category table.
Forum name, description, and last post id come from the forum table.
Last post author and date come from the Post table via the last_post_id (fk) in FORUM
forum.asp displays all the topics within the chosen forum.
query the topic table where the forum you chose = forum_id (fk)
topic.asp display all the posts in a certain forum.
query the post table where the topic equals topic_id (fk)
so, I don't understand how this would result in low performance or speed.
I believe that the speed factor in previous versions resulted in the queries that where used...like the one on default.asp that looped through the recordset that held all the categories, then inside that loop, for each category, it queried the db for all the forums in that category. Pre-getRows queries slowed it down a lot. |
<-- Eric --> |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 09 October 2002 : 20:17:08
|
quote:
Originally posted by Gremlin
I guess if you were going to go down the path of using RI then you would also want to normalise the database a lot more than it is now, this would probably end up with a lot more complex queries being required to just read a single topic.
Hmm...maybe not. What I like less in the current DB strcuture (indexes aside) is probably the unneeded redundancy with "foreign keys" in some tables. Other than that I wouldn't change much... |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 09 October 2002 : 20:30:07
|
fair comment, I was thinking along the lines of normalising forum_memebrs further, and remove all of the duplicate CAT_ID, FORUM_ID columns in the posts and replys and archive tables.
It would be fair to say though that a highly normalised database does introduce more complex queries becuase of the need to JOIN more data. |
Kiwihosting.Net - The Forum Hosting Specialists
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 09 October 2002 : 20:39:58
|
quote:
Originally posted by Gremlin
fair comment, I was thinking along the lines of normalising forum_memebrs further, and remove all of the duplicate CAT_ID, FORUM_ID columns in the posts and replys and archive tables.
Well I hadn't thought about the members tables, but the CAT_ID and FORUM_ID were what I had in mind when I talked about "foreign keys". Anyway splitting FORUM_MEMBERS is a possiblity, indeed, though probably for performance reasons only.
quote:
It would be fair to say though that a highly normalised database does introduce more complex queries becuase of the need to JOIN more data.
Yeah, I have to agree with that. It's hard to find a decision that brings only advantages  . |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Reinsnitz
Snitz Forums Admin
USA
3545 Posts |
Posted - 09 October 2002 : 22:02:51
|
This is exactly what I was talking about... and the "Joins" and stuff are what drags the performance down... though RI would speed up and simplify deletes... you lose to much on the user end to justify the change.
|
Reinsnitz (Mike) |
|
|
|
GauravBhabu
Advanced Member
4288 Posts |
Posted - 09 October 2002 : 23:17:57
|
The queries with Joins (using the JOIN and ON) may be complex but how can they be drag on performance. Joins are being used anyway in the WHERE Clause. Using the Joins in FROM clause seems to be more efficient then in WHERE clause???
[quote]
The rows selected by a query are filtered first by the FROM clause join conditions, then the WHERE clause search conditions, and then the HAVING clause search conditions. Inner joins can be specified in either the FROM or WHERE clause without affecting the final result. |
|
|
|
Reinsnitz
Snitz Forums Admin
USA
3545 Posts |
Posted - 09 October 2002 : 23:28:35
|
the real drag comes in when you have 100 users hiting the same 4 tables for information constantly at the same time...
with a couple it's easy... and faster to go the route your proposing, but when you get the volume we have for this product... it bogs it down... simple access to limited tables renders faster speeds :) |
Reinsnitz (Mike) |
|
|
|
e3stone
Average Member
USA
885 Posts |
Posted - 09 October 2002 : 23:37:59
|
topics.asp starting @ line #96
'## Forum_SQL - Get original topic and check for the Category, Forum or Topic Status and existence
strSql = "SELECT M.M_NAME, M.M_RECEIVE_EMAIL, M.M_AIM, M.M_ICQ, M.M_MSN, M.M_YAHOO" & _
", M.M_TITLE, M.M_HOMEPAGE, M.MEMBER_ID, M.M_LEVEL, M.M_POSTS, M.M_COUNTRY" & _
", T.T_DATE, T.T_SUBJECT, T.T_AUTHOR, T.TOPIC_ID, T.T_STATUS, T.T_LAST_EDIT" & _
", T.T_LAST_EDITBY, T.T_LAST_POST, T.T_SIG, T.T_REPLIES" & _
", C.CAT_STATUS, C.CAT_ID, C.CAT_NAME, C.CAT_SUBSCRIPTION, C.CAT_MODERATION" & _
", F.F_STATUS, F.FORUM_ID, F.F_SUBSCRIPTION, F.F_SUBJECT, F.F_MODERATION, T.T_MESSAGE"
if CanShowSignature = 1 then
strSql = strSql & ", M.M_SIG"
end if
strSql = strSql & " FROM " & strActivePrefix & "TOPICS T, " & strTablePrefix & "FORUM F, " & _
strTablePrefix & "CATEGORY C, " & strMemberTablePrefix & "MEMBERS M " & _
" WHERE T.TOPIC_ID = " & Topic_ID & _
" AND F.FORUM_ID = T.FORUM_ID " & _
" AND C.CAT_ID = T.CAT_ID " & _
" AND M.MEMBER_ID = T.T_AUTHOR "
set rsTopic = Server.CreateObject("ADODB.Recordset")
rsTopic.open strSql, my_Conn, adOpenForwardOnly, adLockReadOnly, adCmdText
that's a 4 table join if my addition is correct. So, we haven't actually cut the number of joins down by not using RI. I don't understand how using RI would increase the number of joins over what we already have. |
<-- Eric --> |
|
|
|
GauravBhabu
Advanced Member
4288 Posts |
Posted - 09 October 2002 : 23:51:44
|
quote:
Originally posted by reinsnitz
the real drag comes in when you have 100 users hiting the same 4 tables for information constantly at the same time...
with a couple it's easy... and faster to go the route your proposing, but when you get the volume we have for this product... it bogs it down... simple access to limited tables renders faster speeds :)
The Joins are still there and all the 4 tables are still being hit. |
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 10 October 2002 : 00:34:55
|
| Remember Mike is saying this was the original rational for not using them some 2-3 years ago ?, things in the code have changed considerably since then. |
Kiwihosting.Net - The Forum Hosting Specialists
|
|
|
|
work mule
Senior Member
USA
1358 Posts |
Posted - 10 October 2002 : 02:56:11
|
Sorry for the length, but there's a lot of stuff covered in this topic. I had to go do some research to check my P's and Q's before I posted. 
Cascading referential integrity hasn't always been an option with SQL Server (SQL Server 7.0 and prior versions do not support cascading deletes or updates) nor other database systems. One of the long-standing requirements imposed by the developers has been to make this work across the widest assortment of databases. Most likely, some of the same reasons given for why they don't have stored proc support for SQL Server 7/2K can also be applied to why FK constraints and RI aren't used. In it's current state, Snitz provides the same functionality across all database systems without introducing additional complexity to the code.
quote:
There are no foreign keys anywhere in the database.
Going back to the original post, this whole topic is more about constraints then it is about relationships. There are foreign keys in the database, but Visio doesn't identify them as FK's because there aren't any FK constraints which it can use to identify FK's. In the case of Snitz, primary and foreign keys and relationships are managed at the application layer. If you were to lay this out on paper, you'd pencil them in.
Referential Integrity in Microsoft SQL Server (old, but applicable)
http://www.craigsmullins.com/sql_ref.htm
Using Constraints, Defaults, and Null Values
http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_04_3p2s.asp?frame=true
One caveat with defining all of the relationships/contstraints in SQL Server is that when you create these constraints, you're adding additional indexes (which take additional space). Not to say that they aren't already there, but something to take into consideration. When creating a constraint, you don't always have the same options which are available to you when creating an index. When creating FK's, you should make sure that indexes exist on your FK's.
Indexes
http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_05_30s5.asp?frame=true
SQL Server automatically creates indexes for certain types of constraints (for example, PRIMARY KEY and UNIQUE constraints). You can further customize the table definitions by creating indexes that are independent of constraints.
The performance benefits of indexes, however, do come with a cost. Tables with indexes require more storage space in the database. Also, commands that insert, update, or delete data can take longer and require more processing time to maintain the indexes. When you design and create indexes, you should ensure that the performance benefits outweigh the extra cost in storage space and processing resources.
Don't Let Referential Integrity Degrade Your Database Performance
http://www.inquiry.com/techtips/oracle_pro/10min/10min0102/10min0102.asp
So far the discussion has been about FK's in regards to categories/forums/topics/replies. What about the members table? Technically the MemberID is a FK in quite a few tables. If someone put in a FK for the member id with cascading referential integrity, there would be problems if someone deleted a member record. Bye, bye topics (topic author), replies, etc. One big cascading nightmare if you're not careful.
To finish up, normalizing a database is good, but there's a point when it can be taken too far. Traditionally during the logical design, you're going to attempt to normalize as much as you can. However when it comes to physical design, sometimes denormalization is beneficial for performance. In a transaction heavy application, normalization is best. In a report/read type application, some denormalization is good to have. A forum application traditionally has more reading then posting.
Database Performance
http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_02_2205.asp
Databases can be overnormalized, which means the database is defined with numerous, small, interrelated tables. When the database is processing the data in these tables, it has to perform a great deal of extra work to combine the related data. This extra processing can reduce the performance of the database. In these situations, denormalizing the database slightly to simplify complex processes can improve performance.
In Database Design there's a good bit of text referring to this. The author is all for normalizing as much as you can and ensuring that you've looked at all options before denormalizing, but there are times when you may need to denormalize. The cost of joins, however slight it may be, is real.
Database Design, pg 190
Although most successful databases are normalized to some degree, there is one substantial drawback of a normalized database: reduced database performance. ... A normalized database requires more CPU, memory, and I/O to process transactions and database queries than unnormalized and denormalized databases require. A normalized database must locate the requested tables and then join the data from the tables to either get the requested information or to process the desired data.
Database Design, pg 282
The rationale for denormalization is that there's a performance cost to joining two or more tables based on the equality of primary key or other parent and foreign key columns so you can put columns from each of these tables on a screen or report at the same time. It takes time to do the join. If that join time is too long for the users, the table structures can be changed to provide the data required without the join. This could be accomplished by adding redundant columns in one of the tables with the required data or recombining tables that were broken up by normalization so that data is now in the same table and doesn't require a join. The cost of such denormalization is managing and ensuring the update and consistancy of the redundant or derived data.
|
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 10 October 2002 : 03:02:11
|
| Good stuff ! |
Kiwihosting.Net - The Forum Hosting Specialists
|
|
|
|
e3stone
Average Member
USA
885 Posts |
Posted - 10 October 2002 : 03:04:51
|
To: The Member (Formerly Known as Work Mule)
Long post, yes, but many good articles. I haven't had a chance to read them all, but I'll read them tomorrow morning. I actually think I have that book somewhere around my apartment, too. Thanks for the info. |
<-- Eric --> |
|
|
|
Topic |
|
|
|
Topic Locked
