| Author |
 Topic  |
|
h20
Starting Member
39 Posts |
 Posted - 17 November 2000 : 12:55:50
|
Gor...
Whatever format we choose for the date and time... I think using this format:
date+time+userid
is the only way we are going to get this forum cross-db. There really is no performance advantage you using sequential numbers for the ID. Sure it is the most intuitive approach, but I think performance and cross compatibility is far more important. Besides, if we do use the date+time+userid format, the ID calculation is done in the asp code... less trips to the db... less processing on the server side... equates to faster posting.
We may even want to include something like this, hang on to your boots:
forumid+topicid+date+time+userid
This too would increase the querying time. Think about it, you would not need to query multiple tables to display the replies -> for a topic -> for a forum. You could tell by the message id what forum and topic it belonged to. Instead of having to first query the topics once a forum is clicked.. than querying the replies once a topic is clicked... which takes 2 trips to the db.. you could do it all in one trip.... using the forumid+topicid+date+time+userid.
For those of you familiar with creating logical db designs... you will probably notice that this is basically changing the forum->topic->reply relationship to a classification structure... or ISA relationships...
< |
|
|
HuwR
Forum Admin
United Kingdom
20593 Posts |
Posted - 17 November 2000 : 13:02:10
|
See reply to other post
'Resistance is futile'< |
 |
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 17 November 2000 : 14:17:30
|
Why do you keep starting new topics on the same discussion?
======
Doug G
======< |
|
|
|
gor
Retired Admin
Netherlands
5511 Posts |
Posted - 17 November 2000 : 15:22:02
|
quote:
Why do you keep starting new topics on the same discussion?
h20,
I locked the other post (again).
Doug is right on this, it is much easier to keep replying to the same topic since it is all the same discussion.
Don't worry about someone not seeing your post, I read most of the topics out here. 
Pierre Gorissen
Even if you're on the right track,
you'll get run over if you just sit there.
Will Rogers< |
|
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 17 November 2000 : 15:52:38
|
quote:
We may even want to include something like this, hang on to your boots:
forumid+topicid+date+time+userid
This would not be a good idea.
======
Doug G
======< |
|
|
|
gor
Retired Admin
Netherlands
5511 Posts |
Posted - 17 November 2000 : 16:16:36
|
hmm, the point about problems arising when using the YYYYMMDDhhmmss ID when having Daylight saving time is a valid one (Dave, it is international, not all countries us it, but a lot do, here in Europe i.e. they do).
But with the sequential number thing you would have to be 200% sure locking is supported and handled ok in the code because you do a write and read that have to be uninterrupted otherwise you would also risk having duplicates when there are a lot of concurrent users.
But afterall this is a Development Discussion: two options that both look good at first sight, but could both cause problems. So lets see if we can find option three.
Pierre Gorissen
Even if you're on the right track,
you'll get run over if you just sit there.
Will Rogers< |
|
|
|
h20
Starting Member
39 Posts |
Posted - 17 November 2000 : 17:41:08
|
quote:
quote:
We may even want to include something like this, hang on to your boots:
forumid+topicid+date+time+userid
This would not be a good idea.
======
Doug G
======
Doug any reason why? I gave several reasons why it would be a good idea...
< |
|
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 17 November 2000 : 20:56:17
|
A number of reasons come to mind. Keep in mind this discussion is regarding the creation of a surrogate ID key for tables. So if you used forumid+topicid+date+time+userid, would each of those component id's be made up of forumid+topicid+date+time+userid? Or what other id would be used?
joe: 'seemes like a vicious cycle'
bob: 'remind me to kilocycle'
joe" 'ooh, that hertz'
(sorry, I couldn't resist )
Next, assuming the combination of id's is made up of nice clean id's, how do you separate forumid of 1 and topicid of 111 from forumid of 11 and topicid of 11?
Next, what if the source tables for any of these id's are changed manually? Say an admin went in to a topic table and manually changed the id. The combination id no longer is valid for any lookups, or worse, returns incorrect data.
Plus, this would pretty much eliminate any chance of ever making code to move a topic or forum.
Personally, I use surrogate ID's of sequential numbers in all DB's I do. Mostly they are in MS so I have Identity or Autonumber available, otherwise I use a counter table as described. DB's that don't support SQL92 transactions or equivalent are not on my radar, those that do have no concurrency problems with a counter table.
If I were to use some other id than a straight number, I'd use a GUID. The date and time idea is OK on one server, but not only daylight savings time changes interfere, but what if you emailed a copy of your db to an associate in a different time zone? What if you moved your server to a different time zone? etc. etc.
======
Doug G
======
Edited by - Doug G on 17 November 2000 20:59:19
Edited by - Doug G on 17 November 2000 21:00:04< |
|
|
|
Fez
Starting Member
USA
25 Posts |
Posted - 19 November 2000 : 13:34:51
|
I can see the point in all of the methods talked about, but this is getting to be like the Presidential Election and we need to come to a final vote.
Here's my spin:
1. The existing system is already using the numeric system, so for the sake of compatibility, I think we should continue that. Some people will be converting those databases and this would be an easy transition.
2. A simple GetNextID() call should get the next sequential ID. The next ID does not have to be for a specific table, but a parameter such as GetNextId("forum_members", would be possible if we want to pick the next ID in each table. I don't think we'll need that. Returning the next ID is good enough for me regardless of what table the last one was used for.
3. I think an Application variable would give us a simple pain free way of handling the locks. When the first session hits, it should do the database query to find the next sequential number (again perhaps one for each major table)... For the rest of the day, or until the application shuts, just give out the next sequence. The var locks, and unlocks so there is never two given the same number.
4. Any time an application shuts, the highest value can be dumped to a table, or we can simply rely on the application start program to query all our tables to get the next number... should take less than a second, and it's only when the server restarts.
5. By using this approach, since there is no hits to a database (except the first query since a restarted server), the speed will not suffer, but there will be some numbers that are requested, but never used if the record is not posted. Shucks.
I don't know about you, but I need to get some of my own code working, so I'm going to write one just like this.
Fez
Developer of CouponPages.Com< |
|
|
|
Fez
Starting Member
USA
25 Posts |
Posted - 19 November 2000 : 15:21:35
|
I've had second thoughts about some of what I said here and in the VFP forum.
I was originally going to code this as a section inside of global.asa, and then put the GetNextID() function inside of inc_functions.asp.
On second thought I think the simplest method is to code GetNextID and place all of the logic in one place.
When you need the next id, just call GetNextID().
When GetNextID is run:
Get the value of Application("NextIDSeq")
Two scenarios exist: 1) It has a value, 2) it has no value (has not been initialized)
1. If it has a value:
- Lock the application variable
- add one
- unlock
- return that value
2. If it has no value:
- Query the id fields in all tables involved.
- Take the highest value + 1
- Lock the application
- store the value
- Return it.
Fez
Developer of CouponPages.Com< |
|
|
|
HuwR
Forum Admin
United Kingdom
20593 Posts |
Posted - 19 November 2000 : 15:56:06
|
quote:
On second thought I think the simplest method is to code GetNextID and place all of the logic in one place.
When you need the next id, just call GetNextID().
Definately the best idea.
quote:
When GetNextID is run:
Get the value of Application("NextIDSeq")
Two scenarios exist: 1) It has a value, 2) it has no value (has not been initialized)
Under what circumstances would 2 occur, or is this a one only occurence when introducing the code?
If not, we could still get problems with people retreiving the same value from the db,even though the application is locked, you will have to maybe add some extraa checks ie.
2. If it has no value:
- Query the id fields in all tables involved.
- Take the highest value + 1
- Lock the application
- Check the value of Application("NextIDSeq") isn't what I am about to write ?
- store the value
- Return it.
Fez
Developer of CouponPages.Com
[/quote]
'Resistance is futile'
Edited by - huwr on 19 November 2000 15:57:05< |
|
|
|
Fez
Starting Member
USA
25 Posts |
Posted - 19 November 2000 : 16:38:31
|
# 2 is only for the first request. From that point on, as long as the function is called correctly, the value will exist, and because the application is locked for a microsecond, nobody else would beat you to it, even if you don't post the record until a bit later.
Here's some code:
'
' this function can bse used to look for the newest key in any table
' It uses an Application Variable for speed, and less Database Hits.
' However if you plan on adding records outside of this function, remove that code
Function GetNextID(table, keyfield)
if IsEmpty(application("Next_" & table)) then
set tmp_rs = Server.CreateObject("ADODB.RECORDSET")
tmp_rs.Open table, strconnect, 1, 1, 2
tmp_rs.Movelast
GetNextID = tmp_rs(keyfield)
if GetNextID > 0 then GetNextID = GetNextID +1 else GetNextID = 1
Application.Lock
Application("Next_" & table) = GetNextID
Application.Unlock
set tmp_rs = nothing
else
Application.Lock
Application("Next_" & table) = Application("Next_" & table) + 1
Application.Unlock
GetNextID = Application("Next_" & table)
end if
End Function
This code allows you to have a different application variable for each table, and it gets the newest ID in each table.
To call it:
My_next_member = GetNextID("FORUM_MEMBERS", "MEMBER_ID")
Or use any other table and key.
I've tested it, and it works. However if you plan on adding records outside the web, or without the use of this function first, then the application variables would be removed from the logic, and you would just go to the table lookup for the next key.... slower.
Fez
Developer of CouponPages.Com< |
|
|
|
HuwR
Forum Admin
United Kingdom
20593 Posts |
Posted - 19 November 2000 : 16:43:16
|
looks good to me, what does everyone else think ?
'Resistance is futile'< |
|
|
|
Fez
Starting Member
USA
25 Posts |
Posted - 19 November 2000 : 16:44:58
|
PLEASE NOTE: In the above post, I used my own connect string variable, for standard Snitz code: use "strConnString" in place of "strconnect"
Also I remind you that with a minor mod, that function can hit the database each time, rather than trusting that nobody has entered an ID without hitting this function first (from a module you may not have remembered to re-code?) The performance hit would still be minor, but if you only intend to use it online, and don't plan on adding records from Access or from other apps... stick to the application variables... they are FAST!
Fez
Developer of CouponPages.Com< |
|
|
|
Fez
Starting Member
USA
25 Posts |
Posted - 19 November 2000 : 16:52:27
|
The main thing to remember about this code is that it requests the next number from an application variable... in a millisecond it's locked and very unlikely two people would get use of it. If you are truly scared, we can lock the value before the query, other processes would be forced to wait. When the application variable is set, it's unlocked and ready for the next user without having to store it in a table.
Once again, the table query would only happen the first time the function is run, unless the server is restarted, but then it would do the query.
The safety net is creating a Primary Key on the field, so that the database would prevent the duplicates. In our worst case scenario, a record would be rejected, and they would re-try. No corrupted keys would be possible as long as the database is set up with that field as a primary key simply because dupes are blocked.
Fez
Developer of CouponPages.Com< |
|
|
|
Reinsnitz
Snitz Forums Admin
USA
3545 Posts |
Posted - 19 November 2000 : 17:38:35
|
Ok... I know there has been other discussion elsewhere... but I'm throwing in my 2 centc here... (one of the drawbacks of spliting up your topic H2O, not many follow the conversation)
Basicaly right now, when a Category/Forum/Topic/Reply is written to the database, it just writes the critical data, then the DB inserts the ID fields that are new within itself (much faster and more efficient than doing it in the ASP code)
I don't see the issue. In Oracle, we write a trigger to autogenerate the ID's. In VFP we can't use a trigger just right... but a guy named Greg has done the work that comes out with the smae result, and it's all database driven. All with extreemly small modifications to the code.
As for speed... we're aiming for elimination of the extreeme <% %> <% %> <% %> problem, and going with limited Response.Write "" code (structured so that it's very easy to follow. This will take care of most of the speed troubles... Gor implimented the nifty config.asp modifications that made the forum go about 4 times faster than it did...
let me know if I missed something critical in this conversation :)
Cheers!
Reinsnitz (Mike)
><)))'>
"I no longer call you servants, because a servant does not know his master's business. Instead I have called you friends..."
-- John 15:15< |
|
|
|
Topic |
|
|
|
Forum Locked
