April 17, 2002 at 2:56 am
Hi,
We need to improve the speed of our processes dramatically. The one way we thought of doing it was to relook at the Indexes.
Here is a typical example of a table:
/****** Object: Table [dbo].[Client_Holding] Script Date: 2002/04/17 09:42:02 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Client_Holding]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Client_Holding]
GO
/****** Object: Table [dbo].[Client_Holding] Script Date: 2002/04/17 09:42:06 ******/
CREATE TABLE [dbo].[Client_Holding] (
[Portfolio_Number] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Currency_Code] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Instrument_Code] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Holding] [money] NULL ,
[Clean_Book_Value] [money] NULL ,
[Market_Value_Interest] [money] NULL ,
[All_In_Market_Value] [money] NULL ,
[Unrealised_Profit_Loss] [money] NULL ,
[Unsettled_Interest] [money] NULL ,
[Average_Cost] [decimal](18, 5) NULL ,
[Average_Per_Book_Value] [money] NULL ,
[Prev_Per_Holding] [money] NULL ,
[Prev_Per_Average_Cost] [decimal](18, 5) NULL ,
[Equivalent_Exposure] [money] NULL ,
[Cash_Exposure] [money] NULL ,
[Bccy_Average_Rate] [decimal](18, 10) NULL ,
[Bccy_All_In_Market_Value] [money] NULL ,
[Bccy_Clean_Book_Value] [money] NULL ,
[Bccy_Market_Value_Interest] [money] NULL ,
[Bccy_Prev_Book_Value] [money] NULL ,
[Bccy_Total_Unreal_PL] [money] NULL ,
[Bccy_Asset_Unreal_PL] [money] NULL ,
[Bccy_Unsettled_Int] [money] NULL ,
[Bccy_Forex_Unreal_PL] [money] NULL ,
[CGT_Book_Value] [money] NULL ,
[CGT_Realised] [money] NULL ,
[CGT_Unrealised] [money] NULL ,
[CGT_S24_Adjustment] [money] NULL ,
[Bccy_Equivalent_Exposure] [money] NULL ,
[Bccy_Cash_Exposure] [money] NULL ,
[Reporting_Period_Date] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [Indx1] ON [dbo].[Client_Holding]([Portfolio_Number], [Currency_Code], [Instrument_Code], [Reporting_Period_Date]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Client_Holding] WITH NOCHECK ADD
CONSTRAINT [PK_Client_Holding] PRIMARY KEY NONCLUSTERED
(
[Portfolio_Number],
[Currency_Code],
[Instrument_Code],
[Reporting_Period_Date]
) ON [PRIMARY]
GO
The things that define the uniqueness of that table is defined in the primary key. In queries / stored procs we use [Reporting_Period_Date] and [Portfolio_Number] the most as the limiting factors.
Finally, my question:
Would it make a diference in the above scripted index, to move [Reporting_Period_Date], [Portfolio_Number] as always the first entries in the Index field list. And then make sure the index sorts by [Reporting_Period_Date]? Would that speed things up?
Thanks!
April 17, 2002 at 5:10 am
Ok you may hurt yourself with this overall since these are in truth the same indexes. I suggest drop Indx1 and make PRIMARY KEY NONCLUSTERED into PRIMARY KEY CLUSTERED.
Now based on what you said, take and put the most queried item in first (the first column in an index is the only one statistics are stored for) then place them in order of uniqueness.
Example if Instrument_Code code is more unique than Portfolio_Number and is less unique than Currency_Code then do like so
ALTER TABLE [dbo].[Client_Holding] WITH NOCHECK ADD
CONSTRAINT [PK_Client_Holding] PRIMARY KEY CLUSTERED
(
[Reporting_Period_Date],
[Currency_Code],
[Instrument_Code],
[Portfolio_Number]
) ON [PRIMARY]
GO
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 17, 2002 at 6:09 am
You should also consider changing to a binary sort order. This will defintely give you some improvement.
April 17, 2002 at 6:19 am
In addition to What Antares686 had said, I would like to suggest some other things which could be used to improve the performance.
Change Varchar (6) and Varchar(3) into Char if possible.
Try to use smallmoney instead of Money if a column holds small values.
Partition the data horizontally if possible. and create an indexed view for data retrival.
Cheers,
Prithiviraj Kulasingham
http://preethiviraj.blogspot.com/
April 17, 2002 at 6:32 am
Say then the first index have [Reporting_Period_Date] first, so that statistics is stored for it; would there then be any value to create a second index where [Portfolio_Number] is the first column, since many queries also use that field and statistics is contained?
Thanks!
April 17, 2002 at 8:08 am
Partioning is sometimes a good idea, but only after you've exhausted your other options. I agree that the primary index should be the clustered one in this case.
Andy
April 17, 2002 at 9:49 am
The best thing to do after my initial suggestion is run profiler to catch the queries run most often and see what index additions may make since. However with you question on the secound item, if it is used extremely often you may want to test a with and without that index to see what happens. Even though the statistics are only stored ifor the frst column it may still use it since the overall statistics are better. With that fact it may not even use a new index but instead scan the other then you have to use hints to overrule. Queries plans are first based on find the best statistical index with the highest number of uniquenesses (this is why bit fields are not to be used).
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 18, 2002 at 10:34 am
I agree with Antares, I would capture the queries through SQL Profiler and look for missing stats. In addition run a small (few minutes) trace through the Index tuning wizard. unlike other systems SQL Servers Tuning wizard is very good.
-JG
-JG
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply