| Author |
 Topic  |
|
Chiz
Junior Member
245 Posts |
 Posted - 01 May 2002 : 13:08:05
|
Am I correct with the term--page locks causes a page to take forever to load and then times out?
Anyway, it's what I currently experience in our forum. The major MOD's I remember adding was Categorized Icons, Active Users 4 and Announcement MOD's. It's just lately that I encountered the hangs after installing the latter 2 MOD's.
I assume it's the modification with the main forum that causes it to lock since the mobile edition of the forum and the main site load fine. You can find the links to both forums on my sig below.
Appreciate any help from you guys. Thanks.
My websites: PalmVenue :: PV Mobile
My Snitz MODs: Categorized Icons
Edited by - Chiz on 01 May 2002 13:09:58 |
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 01 May 2002 : 20:09:16
|
Huwr reported this problem and the fix for AU4 anyway. I hadn't had the problem, but I went ahead and added an AutoIdent Column and created and Index on it just for safteys sake.
http://forum.snitz.com/forum/topic.asp?TOPIC_ID=26871
www.daoc-halo.com |
 |
|
|
Nathan
Help Moderator
USA
7664 Posts |
|
|
Nathan
Help Moderator
USA
7664 Posts |
Posted - 01 May 2002 : 20:12:27
|
Gremlin, can you spell out for me what I need to do to create that "clustered" index thing in the mod?
As if you were explaining to a first grader how to add it 
 Nathan Bales
Snitz Exchange | Do's and Dont's
Edited by - Nathan on 01 May 2002 20:13:11 |
|
|
|
Chiz
Junior Member
245 Posts |
Posted - 01 May 2002 : 21:55:13
|
Guys, thanks for the links.
I've read the threads, don't know if I got it right. You mean to possible remedy it I will just have to set the MEMBER_ID column to Yes (Duplicates OK) bec. guests will always have a value of 0. Am I correct?
Also, it was mentioned that for low volume boards, this isn't necessary. Our board reached a record of 18 active users at one time, do we consider this high volume cause it locked?
Thanks in advance.
My websites: PalmVenue :: PV Mobile
My Snitz MODs: Categorized Icons
Edited by - Chiz on 01 May 2002 22:03:43 |
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 01 May 2002 : 21:55:39
|
Does the DBS tool support creation of Indexs ?
www.daoc-halo.com |
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 01 May 2002 : 21:57:08
|
quote:
Guys, thanks for the links.
I've read the threads, don't know if I got it right. You mean to possible remedy it I will just have to set the MEMBER_ID column to Yes (Duplicates OK) bec. guests will always have a value of 0. Am I correct?
Also, it was mentioned that for low volume boards, this isn't necessary. Our board reached a record of 18 active users at one time, is this likely to lock?
Thanks in advance.
My websites: PalmVenue :: PV Mobile
My Snitz MODs: Categorized Icons
I added an extra column, called it AUID or something and added the index manually via Enterprise Manager (probably wasn't the best way of doing it though, I'll take another look at it now, the Index would I suspect be better on both the MEMBER_ID and NAME columns).
Nathan when I work out how/if the DBS setup handles index's I'll just try and modify your DBS file for you, that should make it a little easer for ya.
www.daoc-halo.com
Edited by - Gremlin on 01 May 2002 21:58:47 |
|
|
|
Chiz
Junior Member
245 Posts |
Posted - 01 May 2002 : 22:13:48
|
Again, thanks for the help guys.
Another question though,
quote:
I added an extra column, called it AUID or something and added the index manually via Enterprise Manager (probably wasn't the best way of doing it though, I'll take another look at it now, the Index would I suspect be better on both the MEMBER_ID and NAME columns).
Is that a separate index for each column, i.e., AUID, MEMBER_ID , NAME ? Or Index=AUID+MEMBER_ID+NAME?
My websites: PalmVenue :: PV Mobile
My Snitz MODs: Categorized Icons
|
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 01 May 2002 : 22:46:30
|
quote:
Is that a separate index for each column, i.e., AUID, MEMBER_ID , NAME ? Or Index=AUID+MEMBER_ID+NAME?
I think you mean each 'row' not column. Adding an extra column will effect all rows anyway, so for instance by adding AUID as a column, every row in the database will then have an AUID field.
After looking at it again I don'think you need to add an extra column, just placing the index on MEMBER_ID should be ok
CREATE CLUSTERED
INDEX [FORUM_AU_MEMBER_ID] ON [dbo].[FORUM_ACTIVE_USERS] ([MEMBER_ID])
www.daoc-halo.com |
|
|
|
Chiz
Junior Member
245 Posts |
Posted - 01 May 2002 : 23:02:01
|
quote:
CREATE CLUSTERED INDEX [FORUM_AU_MEMBER_ID] ON [dbo].[FORUM_ACTIVE_USERS] ([MEMBER_ID])
Is this SQL Server-specific? Tried it on MS-Access and it caused a Syntax error in CREATE TABLE statement. I got to worked when I removed the CLUSTERED keyword.
My websites: PalmVenue :: PV Mobile
My Snitz MODs: Categorized Icons
|
|
|
|
Nathan
Help Moderator
USA
7664 Posts |
Posted - 02 May 2002 : 00:32:12
|
No need to use a dbs file, the AU mod also has a db modifications scipt.
It should not be nesisary for Access. Access can't even handle enough traffic to worry about it right?
quote:
CREATE CLUSTERED
INDEX [FORUM_AU_MEMBER_ID] ON [dbo].[FORUM_ACTIVE_USERS] ([MEMBER_ID])
Can you explain how this works Gremlin?
Nathan Bales
Snitz Exchange | Do's and Dont's |
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 02 May 2002 : 01:59:09
|
Access doesn't support true clustered indexes, however when you run a DB compact under access if a table has a primary key defined, it will copy the records in primary key order, which makes reading ahead more efficient. This produces an effect similar to clustered indexes in SQL Server; however, unlike true clustered indexes, Access doesn't maintain primary key order when users add, delete, or modify records after the database is compacted. If a table doesn't have a primary key, Access copies records in the order that the records are stored on disk.
I think like youd highlighted Nathan that this is probably more a sqlserver issue than anything else due to the expected high volume of users, under most sites running access the hit rate is probably low enough not to cause any problems.
I don't see any harm however in adding the index.
For access the syntax for adding an index should be (as you discovered Chiz) exactly the same as for sqlserver, just remove the "Clustered" operand.
www.daoc-halo.com |
|
|
|
Nathan
Help Moderator
USA
7664 Posts |
Posted - 02 May 2002 : 02:17:13
|
This article from ASP Alliance makes me think that a non-clustered index would be more appropriate for this table.
quote:
Which Indexes to Choose?
- Non-Clustered Indexes: only on tables/columns where many modifications are made (such as on OLTP [Online Transaction Processing] systems) and where the values are close to unique.
- Clustered Indexes: only on tables/columns where little or no modifications are made (such as on OLAP [Online Analytical Processing] systems) and where the values are not unique.
. . .
Nathan Bales
Snitz Exchange | Do's and Dont's |
|
|
|
Chiz
Junior Member
245 Posts |
Posted - 02 May 2002 : 02:58:50
|
OK. Got the sort key saved in the test environment's database although I can't attest that it really did solved the page locking problem. I will do the same in the prod environment shortly and see what I can find. I will keep you posted guys. Thanks for all the help.
BTW, for the benefit of MS-Access db users here's the SQL statement I used:
CREATE INDEX [FORUM_AU_MEMBER_ID] ON [FORUM_ACTIVE_USERS] ([MEMBER_ID])
My websites: PalmVenue :: PV Mobile
My Snitz MODs: Categorized Icons
|
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 02 May 2002 : 05:39:14
|
For every article saying you should use a clustered index in situations x,y,z only I can usually find another one saying the exact opposite  Personally I've just used whatever seems to work best, and rely on the index tuning wizard in sqlserver when I'm not sure, when i ran a workload file through it suggested the above index so thats my reasoning behind going with it.
www.daoc-halo.com |
|
|
|
Chiz
Junior Member
245 Posts |
Posted - 02 May 2002 : 09:53:03
|
Status Report
After adding the index, site loads ok even with 11 simultaneous active users. Still very much early to conclude, though. I'm going to evaluate it more. I'll keep you guys posted.
My websites: PalmVenue :: PV Mobile
My Snitz MODs: Categorized Icons
|
|
|
|
Topic |
|
Topic Locked
