| Author |
 Topic  |
|
|
grazman
Junior Member
USA
193 Posts |
 Posted - 10 September 2000 : 12:17:17
|
Ok, since I was doing all the posting on changing data types I thought I'd be bold and see what happened. I made a these changes to my test machines and after testing rolled it out to my production machine. And here's what I did:
In CATEGORY, FORUM, TOPIC and REPLY I changed all date fields to char(14). I made a point not to choose nchar as my data type. nchar, ntext and nvarchar are used to allow unicode data in a field. This is mainly used for global (non-US, non-ASCII) characters in a field. It also means that each character takes 2 bytes rather than the usual one. While that's just fine for the comments, subjects, etc., it really isn't needed for time stamps and IP address. I think this is one of those things that the Upsizing Wizard does to all character based fields.
I also changed all the IP fields to varchar(15). A strong arguement could me made for making them char(15) but that's a topic for another day.
I also worked on the indexes. I actually deleted about half of them. Below you the list of indexes I have on the CATEGORY, FORUM, TOPIC and REPLY tables. These are the only indexes I have on these tables. Most of the indexes are created with a CREATE INDEX statement however some are created with a CONSTRAINT. At this point I'd really suggest you manually make your indexes match these rather than using a script. What you see below is my extractions from a script. Again, please don't try to run this.
My production database has 237 topics and 135 replies in it right now. The TOPIC and REPLY table took up 896K of space. After changing datatypes and indexes, these tables now take up 672K for a 25% savings in space. There are also half the number of indexes that need to be updated for each insert.
If you are looking for something you can do today to improve your SQL Server based forums, I would add the two indexes I've identified in green. If you copy just those two CREATE INDEX statements into Query Analyzer they should run just fine. Or you could create them using Entrprise Manager. They should be just as beneficial to Access as they are to SQL Server. Just adding those two indexes and nothing else should speed read performance by 2-5 times as much and eliminate a great number of tables scans (the bane of SQL Server).
Posting this in two parts since a single post failed :(
<font color=blue><b>SQLTeam.com</font id=blue></b> - For SQL Server Developers and Administrators
Edited by - grazman on 10 September 2000 12:20:57 |
|
|
grazman
Junior Member
USA
193 Posts |
Posted - 10 September 2000 : 12:17:47
|
<b>[aaaaaFORUM_CATEGORY1_PK] PRIMARY KEY NONCLUSTERED
(
[CAT_ID]
) WITH FILLFACTOR = 90 ON [PRIMARY] </b>
<font color=red>-- This is the Primary Key on the CATEGORY table and I left it unchanged
-- Access seems to set these up with a fill factor of 90. Mine are set
-- with a fillfactor of 0 (or none defined).</font id=red>
<b>CONSTRAINT [aaaaaFORUM_REPLY1_PK] PRIMARY KEY NONCLUSTERED
(
[CAT_ID],
[FORUM_ID],
[TOPIC_ID],
[REPLY_ID]
) WITH FILLFACTOR = 90 ON [PRIMARY] </b>
<font color=red>-- This is the Primary Key on the REPLY table and I left it unchanged</font id=red>
<b>CONSTRAINT [aaaaaFORUM_TOPICS1_PK] PRIMARY KEY NONCLUSTERED
(
[CAT_ID],
[FORUM_ID],
[TOPIC_ID]
) WITH FILLFACTOR = 90 ON [PRIMARY] </b>
GO
<font color=red>-- This is the Primary Key on the TOPICS table and I left it unchanged</font id=red>
<b>CONSTRAINT [aaaaaFORUM_FORUM1_PK] PRIMARY KEY NONCLUSTERED
(
[CAT_ID],
[FORUM_ID]
) WITH FILLFACTOR = 90 ON [PRIMARY] </b>
GO
<font color=red>-- This is the Primary Key on the REPLY table and I left it unchanged</font id=red>
<b>CREATE INDEX [FORUM_CATEGORYCAT_STATUS] ON [bgraziano].[FORUM_CATEGORY]([CAT_STATUS]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO</b>
<font color=red>-- I couldn't figure out what this index was doing so I left it unchanged.
-- This table will only have a few records in it so no big deal.</font id=red>
<font color=green>Performance #1</font id=green>
<b>CREATE INDEX [IX_FORUM_REPLY] ON [bgraziano].[FORUM_REPLY]([TOPIC_ID], [R_DATE]) ON [PRIMARY]
GO</b>
<font color=red> -- This index I created purely for performance reason. Since the replies are always show for
-- a specific topic in date ascending order I created an index to match that. This eliminated a table scan.
-- This index alone will probably do more to improve performance than any other.</font id=red>
<b>CREATE INDEX [Reply_ID] ON [bgraziano].[FORUM_REPLY]([REPLY_ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO</b>
<font color=red>--This was here and I left it alone. REPLY_ID isn't actually the primary key but it is
-- a unique identifier of the record.</font id=red>
<b>CREATE INDEX [Topic_ID] ON [bgraziano].[FORUM_TOPICS]([TOPIC_ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO</b>
<font color=red>--This was here and I left it alone. TOPIC_ID isn't actually the primary key but it is
-- a unique identifier of the record.</font id=red>
<b>CREATE INDEX [IX_FORUM_TOPICS_LAST_POST] ON [bgraziano].[FORUM_TOPICS]([T_LAST_POST]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO</b>
<font color=red>-- Another performance index. This was designed to make the "Show Active Topics" query fast.
-- I didn't get as much time to review this as I'd like. I may be able to add TOPIC_ID and make
-- it still faster. This eliminated a table scan.</font id=red>
<font color=green>Performance #2</font id=green>
<b>CREATE INDEX [IX_FORUM_TOPICS_MAIN] ON [bgraziano].[FORUM_TOPICS]([FORUM_ID], [T_LAST_POST]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO</b>
<font color=red>-- This would be my #2 best index for performance. This matches the forum display page.</font id=red>
<b>CREATE INDEX [Forum_ID] ON [bgraziano].[FORUM_FORUM]([FORUM_ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO</b>
<font color=red>--This was here and I left it alone. FORUM_ID isn't actually the primary key but it is
-- a unique identifier of the record.</font id=red></font id=code></pre id=code>
Ok, that's enough for one day. Thoughts?
<font color=blue><b>SQLTeam.com</font id=blue></b> - For SQL Server Developers and Administrators
Edited by - grazman on 10 September 2000 12:20:22 |
 |
|
|
webshorts
New Member
USA
96 Posts |
|
| |
Topic |
|
|
|
Topic Locked
