December 23, 2010 at 12:05 pm
CREATE TABLE [dbo].[DIM_Data_OHLC](
[RecID] [int] IDENTITY(1,1) NOT NULL,
[Symbol] [varchar](10) NOT NULL,
[Date] [datetime] NOT NULL,
[Open] [float] NULL,
[High] [float] NULL,
[Low] [float] NULL,
[Close] [float] NULL,
[Volume] [float] NULL,
[AdjClose] [float] NULL,
[CreatedOn] [datetime] NULL,
[LastUpdate] [datetime] NULL,
[Deleted] [bit] NULL CONSTRAINT [DF_DIM_Data_OHLC_Deleted] DEFAULT ((0)),
[DeletedTime] [datetime] NULL,
CONSTRAINT [PK_DIM_Data_OHLC] PRIMARY KEY NONCLUSTERED
(
[Symbol] ASC,
[Date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
I have the above table with 2.7 million records in it.
I need to search and sort by SYMBOL and DATE.
I download REDGATE FREE script manager and ran this code..
http://www.sqlservercentral.com/articles/Red+Gate+Software/71948/
INDEX FRAGMENTATION
SELECT '[' + DB_NAME() + '].[' + OBJECT_SCHEMA_NAME(ddips.[object_id],
DB_ID()) + '].['
+ OBJECT_NAME(ddips.[object_id], DB_ID()) + ']' AS [Object] ,
i.[name] AS [Index] ,
ddips.[index_type_desc] AS [Index Type],
ddips.[partition_number] AS [Partition Number],
ddips.[alloc_unit_type_desc] AS [Allocation Unit Type],
ddips.[index_depth] AS [Index Depth],
ddips.[index_level] AS [Index Level],
CAST(ddips.[avg_fragmentation_in_percent] AS SMALLINT)
AS [Average Fragmentation (%)] ,
CAST(ddips.[avg_fragment_size_in_pages] AS SMALLINT)
AS [Average Fragment Size (pages)] ,
ddips.[fragment_count] AS [Fragments],
ddips.[page_count] AS [Pages]
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL,
NULL, NULL, 'limited') ddips
INNER JOIN sys.[indexes] i ON ddips.[object_id] = i.[object_id]
AND ddips.[index_id] = i.[index_id]
WHERE ddips.[avg_fragmentation_in_percent] > @fragmentation
AND ddips.[page_count] > @pages
ORDER BY ddips.[avg_fragmentation_in_percent] ,
OBJECT_NAME(ddips.[object_id], DB_ID()) ,
i.[name]
The stats I got for this table with records in it are in the attached txt file. They seam to be poor !
Question: How can I make the INDEXES or table design any better ???
December 23, 2010 at 1:06 pm
If you always need to search by Symbol and Date, change the PRIMARY KEY to [Date] ASC, [Symbol] ASC. I'd even recommend to makes this the clustered index (assuming you can load the data sorted by date and symbol and the date values will be ascending).
The reason for getting such a high fragmentation level is the order of the index: since it's sorted by symbol first, you'll end up with index fragmentation as soon as you enter new values each day.
An alternative might be an index rebuild after data upload (assuming that happens just once per day or so)... In that case you could leave the index as it is right now.
December 23, 2010 at 1:30 pm
Thanks good advice..:-)
How does one rebuild an index..??
and YEs the symbols are out of order, but dates are not !
December 23, 2010 at 1:41 pm
Digs (12/23/2010)
Thanks good advice..:-)How does one rebuild an index..??
and YEs the symbols are out of order, but dates are not !
Have a look at BOL, section "reorganizing indexes".
You can also search this site for index fragmentation and I'm soure you'll find some neat scripts that'll help you.
The question regarding the upload process itself is still open though 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply