August 12, 2010 at 11:58 am
Hi folks,
I got a new database where original design doesn't seem to be good, there are 300 tables, all has the same structures, all has no primary key and index, each table has around 10 million records.
I will work on how to optimize the performance of this database, there will be lots of search function on top of it.
I will start a new database instance, so it's time to start it correctly. My first step would be at least add a primary key to each of the 300 tables.
Here is the script to create the table:
CREATE TABLE [dbo].[AC_00](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Julian] [varchar](3) NULL,
[Date] [datetime] NULL,
[Time] [varchar](8) NULL,
[Transit] [varchar](5) NULL,
[Operator ID] [varchar](8) NULL,
[Type] [varchar](1) NULL,
[Function Code] [varchar](8) NULL,
[Screen] [varchar](4) NULL,
[Terminal] [varchar](12) NULL,
[CIFKey] [varchar](13) NULL,
[Name] [varchar](40) NULL,
[Customer ID] [varchar](15) NULL,
[Product] [varchar](3) NULL,
[Account Number] [varchar](23) NULL,
[ADF Key] [varchar](19) NULL,
[Address] [varchar](40) NULL,
CONSTRAINT [PK_AC_00] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
ID is the new field I added to the original table.
Later on the search most likely would be based on fields like [Account Number] or [Address], how should I create the necessary index? How do I rebuild all the 300 tables index in code after I imported the original data?
I wrote a sp to create the tables and import part of the data in new database. Here is some number:
10 records, 23 seconds, 5M
1000 records, 41 seconds, 580M
10000 records, 3 minutes, 1.1G
It doesn't seem to be a linear growth, how much disk cost and process time should I expect if I import all 10M records?
Thanks lots.
August 12, 2010 at 12:32 pm
If you're in a hurry and have no idea where to start, consider getting someone in to do a performance review, do some tuning and teaching.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply