Table Creation.
-
Hi All,
I have a table the definition of which is listed below. This table will contain some 10M + row. 99% of the work on this table is inserts. Now my question is, is what I have the best way to create this table.
Thanks in advance for any input.
CREATE TABLE [dbo].[Log] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[Date] [datetime] NOT NULL ,
[Thread] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Level] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Logger] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Message] [varchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Exception] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Principal] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Application] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SourceIP] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DestinationIP] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IntermediateIP] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UserAgent] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Event] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SessionKey] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MessageExt] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MethodName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UserName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [Log_IX2] ON [dbo].[Log]([Date]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_Log_Application] ON [dbo].[Log]([Application]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_Log_Event] ON [dbo].[Log]([Event]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_Log_Level] ON [dbo].[Log]([Level]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [IX_Log_Principal] ON [dbo].[Log]([Principal]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
Hope this helps...
Ford Fairlane
Rock and Roll Detective
-
Just a couple of things...
If the Inserts will, more or less, be in order by [Date], then having a Clustered Index on date will be OK, otherwise, the Inserts will be slowed by the reshuffling of data based on the Date column.
You are missing a Primary Key. If there is no column or logical set of columns that could be used for a Primary Key, then consider using the ID column as the surrogate Primary Key. There's a lot of folks that will tell you that this isn't the best idea and that you've likely not designed the table correctly if you have to resort to such a practice. I tend to agree but this IS only a log table and the natural key, whatever it could be, may be repeated. But a table isn't really a table without a Primary Key and the are some performance issues in not having 1, so using the ID as a surrogate Primary Key here should be fine.
I'm not so sure that having an Index on the Event column (maybe others for the same reason) will actually do any good because it's not unique enough. You may want to consider the use of composite keys (ie. one with Application, Event, and Level). The best way to tell would be to create a bit of artifical data in the table and do some performance testing under various index conditions. To avoid artifictially fast readings from the stats building up, you should probably add the following code to the beginning of each test run...
--===== Ensure that the data is NOT cached so we can get a
-- true measure of performance of the code being tested.
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
Finally, there is no way that I would use a TEXT data type in a log of this nature. TEXT datatypes are a pain in the patooti in most cases. If you are using 2005, VARCHAR(MAX) would be OK but in 2000, I would use a VARCHAR instead. If you need the maximum of 8000, consider building a sister table with the ID and a VARCHAR(8000) so you don't violate the maximum row length of 8060.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
You should create a table
without creating indexes, because a primary key itself creates an index
-
Let me try and answer some of the questions.
The Id IS the primary key and yes the ineserst are date based. So the ID as PK and a clustered on date should be suffiecient you think?
Hope this helps...
Ford Fairlane
Rock and Roll Detective -
Yep... for starters...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
It definitely must be not one table.
No doubt you will have repeating values for [Thread] , [Logger], [Principal] , [Application] , [SourceIP] , [DestinationIP] , [IntermediateIP] , [UserAgent] , [Event] , [MethodName], [UserName].
And it's times faster to retrieve ID for supplied name from table dbo.ApplicationUser and record UserId in the log than record varchar(200) value.
_____________
Code for TallyGenerator
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply