July 14, 2009 at 1:43 am
Hi There
I am re-indexing a table for performance purposes. I noticed that there are 4 columns that are always included in the SELECT part of all queries on the table. Also, these four columns are the most common columns in the join predicates.
I need some advice. I noticed that i have two main otpions:
1. Include the 4 columns as part of the clustering key and thus ensure that most queries are covering or at least reduce I/O associated with fetching these columns. Have other NC IXs to cater for the joins. (this plan will obviously make my clustered IX too wide)
2. Include the four columns in each and every one of my NC IXs and thus have a narower clustered IX.
3. Any other suggestion.
Thank you.:-)
July 14, 2009 at 4:51 am
Ok this is always a hard one.
Some advice, I would try and key your Clustered index as narrow as possible.
Also before just creating or changing indexes, have you identified the top 5 worst queries or top 5 most popular queries?
Once you know this then you can tell more about what kind of indexes to create.
If you could give us some query samples and the definitions of your current indexes I could have a look for you.
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
July 14, 2009 at 5:10 am
Below is the table generation script:
CREATE TABLE [dbo].[Vessel](
[ID] [dmVessel] NOT NULL,
[Name] [dmName] NOT NULL,
[SrchName] [dmName] NOT NULL,
[NameType] [dmNameType] NOT NULL,
[ExName1] [dmName] NULL,
[ExName1Srch] [dmName] NULL,
[LloydsNumber] [dmLRNo] NULL,
[MktStatus] [dmMktStatus] NULL,
[TradingStatus] [dmTradingStatus] NOT NULL,
[TradingCategory] [dmTradingCategory] NOT NULL,
[StatusDate] [datetime] NOT NULL,
[VesselType] [dmVesselTypeCode] NOT NULL,
[DisplayType] [dmVesselTypeCode] NOT NULL,
[BPullDisp] [char](5) NULL,
[SubTypeDisp] [dmDisp] NULL,
[BuiltMonth] [dmDateMonth] NULL,
[BuiltYear] [dmDateYear] NULL,
[YardCtry] [dmCountryCode] NULL,
[ShipBuilder] [dmBldrCode] NULL,
[Yard] [dmYardCode] NULL,
[DoubleHull] [dmFlag] NULL,
[DoubleBottom] [dmFlag] NULL,
[DoubleSide] [dmFlag] NULL,
[BulkHeads] [tinyint] NULL,
[Commitment] [dmOrgName] NULL,
[CommitDate] [datetime] NULL,
[CommitDirect] [dmFlag] NULL,
[CommitmadeBy] [dmUsrCode] NULL,
[CommitNote] [dmRemark] NULL,
[ParentOwn] [dmOwnerCode] NULL,
[ManageOwn] [dmOwnerCode] NULL,
[RegistOwn] [dmOwnerCode] NULL,
[FullDescr] [dmBitFlag] NOT NULL,
[TCUpdatedAt] [datetime] NULL,
[TCUpdatedBy] [dmUsrCode] NULL,
[Flag] [dmCountryCode] NULL,
[DwtHigh] [int] NULL,
[GTHigh] [int] NULL,
[NTHigh] [int] NULL,
[Ldt] [int] NULL,
[LdtMetric] [dmFlag] NULL,
[Loa] [real] NULL,
[Lbp] [real] NULL,
[Beam] [real] NULL,
[Tpc] [real] NULL,
[DraftHigh] [real] NULL,
[Engines] [tinyint] NULL,
[EngineMake] [dmName] NULL,
[EngineDes] [dmName] NULL
) ON [PRIMARY]
SET ANSI_PADDING ON
ALTER TABLE [dbo].[Vessel] ADD [EngBuilder] [varchar](80) NULL
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[Vessel] ADD [McoPowerUnit] [dmPwrUnitCode] NULL
ALTER TABLE [dbo].[Vessel] ADD [McoPower] [int] NULL
ALTER TABLE [dbo].[Vessel] ADD [McoRpm] [smallint] NULL
ALTER TABLE [dbo].[Vessel] ADD [Propellors] [tinyint] NULL
ALTER TABLE [dbo].[Vessel] ADD [BowThrusters] [dmFlag] NULL
ALTER TABLE [dbo].[Vessel] ADD [SternThrusters] [dmFlag] NULL
ALTER TABLE [dbo].[Vessel] ADD [SpdConsDisp] [dmDisp] NULL
ALTER TABLE [dbo].[Vessel] ADD [SpeedMax] [real] NULL
ALTER TABLE [dbo].[Vessel] ADD [FuelGrade] [char](30) NULL
ALTER TABLE [dbo].[Vessel] ADD [AuxOilSeaCons] [real] NULL
ALTER TABLE [dbo].[Vessel] ADD [AuxOilSeaType] [dmFuelTypeCode] NULL
ALTER TABLE [dbo].[Vessel] ADD [Class1] [dmClassSocCode] NULL
ALTER TABLE [dbo].[Vessel] ADD [Class2] [dmClassSocCode] NULL
ALTER TABLE [dbo].[Vessel] ADD [ClassNote] [dmRemark] NULL
ALTER TABLE [dbo].[Vessel] ADD [SurveyDates] [varchar](60) NULL
ALTER TABLE [dbo].[Vessel] ADD [DryDockDates] [varchar](30) NULL
ALTER TABLE [dbo].[Vessel] ADD [Imo1] [dmFlag] NULL
ALTER TABLE [dbo].[Vessel] ADD [Imo2] [dmFlag] NULL
ALTER TABLE [dbo].[Vessel] ADD [Imo3] [dmFlag] NULL
ALTER TABLE [dbo].[Vessel] ADD [Imo4] [dmFlag] NULL
ALTER TABLE [dbo].[Vessel] ADD [Decks] [tinyint] NULL
ALTER TABLE [dbo].[Vessel] ADD [DeckArea] [int] NULL
ALTER TABLE [dbo].[Vessel] ADD [DeckAreaNote] [dmRemark] NULL
ALTER TABLE [dbo].[Vessel] ADD [MainDkStrNote] [dmRemark] NULL
ALTER TABLE [dbo].[Vessel] ADD [Osd] [dmFlag] NULL
ALTER TABLE [dbo].[Vessel] ADD [Csd] [dmFlag] NULL
ALTER TABLE [dbo].[Vessel] ADD [Sd] [dmFlag] NULL
ALTER TABLE [dbo].[Vessel] ADD [Grain] [int] NULL
ALTER TABLE [dbo].[Vessel] ADD [Bale] [int] NULL
ALTER TABLE [dbo].[Vessel] ADD [Reefer] [int] NULL
ALTER TABLE [dbo].[Vessel] ADD [Holds] [tinyint] NULL
ALTER TABLE [dbo].[Vessel] ADD [Hatches] [tinyint] NULL
ALTER TABLE [dbo].[Vessel] ADD [HaDims] [dmDisp] NULL
ALTER TABLE [dbo].[Vessel] ADD [HaCover] [dmRemark] NULL
ALTER TABLE [dbo].[Vessel] ADD [OreStr] [dmFlag] NULL
ALTER TABLE [dbo].[Vessel] ADD [HvyStr] [dmFlag] NULL
ALTER TABLE [dbo].[Vessel] ADD [Teu] [int] NULL
ALTER TABLE [dbo].[Vessel] ADD [ReefPoints] [int] NULL
ALTER TABLE [dbo].[Vessel] ADD [ReefPtsType] [dmReefPtsType] NULL
ALTER TABLE [dbo].[Vessel] ADD [Homog14T] [int] NULL
ALTER TABLE [dbo].[Vessel] ADD [Feu] [int] NULL
ALTER TABLE [dbo].[Vessel] ADD [TeuSplit] [int] NULL
ALTER TABLE [dbo].[Vessel] ADD [Cellular] [dmFlag] NULL
ALTER TABLE [dbo].[Vessel] ADD [Trailers] [smallint] NULL
ALTER TABLE [dbo].[Vessel] ADD [LaneMeters] [int] NULL
ALTER TABLE [dbo].[Vessel] ADD [Cars] [int] NULL
ALTER TABLE [dbo].[Vessel] ADD [SternDoor] [dmFlag] NULL
ALTER TABLE [dbo].[Vessel] ADD [BowDoor] [dmFlag] NULL
ALTER TABLE [dbo].[Vessel] ADD [SideDoor] [dmFlag] NULL
ALTER TABLE [dbo].[Vessel] ADD [QtrRamp] [dmFlag] NULL
ALTER TABLE [dbo].[Vessel] ADD [SternRamp] [dmFlag] NULL
ALTER TABLE [dbo].[Vessel] ADD [BowRamp] [dmFlag] NULL
ALTER TABLE [dbo].[Vessel] ADD [SideRamp] [dmFlag] NULL
ALTER TABLE [dbo].[Vessel] ADD [SlewRamp] [dmFlag] NULL
ALTER TABLE [dbo].[Vessel] ADD [IntRamps] [dmFlag] NULL
ALTER TABLE [dbo].[Vessel] ADD [IntLifts] [dmFlag] NULL
ALTER TABLE [dbo].[Vessel] ADD [DeckHgts] [dmRemark] NULL
ALTER TABLE [dbo].[Vessel] ADD [GearDisp] [dmDisp] NULL
ALTER TABLE [dbo].[Vessel] ADD [GearMaxSwl] [real] NULL
ALTER TABLE [dbo].[Vessel] ADD [Gearless] [dmFlag] NULL
ALTER TABLE [dbo].[Vessel] ADD [PassTotal] [int] NULL
ALTER TABLE [dbo].[Vessel] ADD [Berths] [int] NULL
ALTER TABLE [dbo].[Vessel] ADD [Cabins] [int] NULL
ALTER TABLE [dbo].[Vessel] ADD [Crew] [int] NULL
ALTER TABLE [dbo].[Vessel] ADD [Drivers] [int] NULL
ALTER TABLE [dbo].[Vessel] ADD [ReefTempLow] [real] NULL
ALTER TABLE [dbo].[Vessel] ADD [ReefTempHigh] [real] NULL
ALTER TABLE [dbo].[Vessel] ADD [ReefTempCelcius] [dmFlag] NULL
ALTER TABLE [dbo].[Vessel] ADD [ReefTempNote] [dmRemark] NULL
ALTER TABLE [dbo].[Vessel] ADD [Ventilation] [dmBitFlag] NOT NULL
ALTER TABLE [dbo].[Vessel] ADD [VentType] [dmVentTypeCode] NULL
ALTER TABLE [dbo].[Vessel] ADD [AirChanges] [tinyint] NULL
ALTER TABLE [dbo].[Vessel] ADD [AirCirculations] [smallint] NULL
ALTER TABLE [dbo].[Vessel] ADD [TempZones] [tinyint] NULL
ALTER TABLE [dbo].[Vessel] ADD [Compartments] [tinyint] NULL
ALTER TABLE [dbo].[Vessel] ADD [PalletCap] [int] NULL
ALTER TABLE [dbo].[Vessel] ADD [LiquidCubic] [int] NULL
ALTER TABLE [dbo].[Vessel] ADD [SSteelCubic] [int] NULL
ALTER TABLE [dbo].[Vessel] ADD [Tanks] [tinyint] NULL
ALTER TABLE [dbo].[Vessel] ADD [Grades] [tinyint] NULL
ALTER TABLE [dbo].[Vessel] ADD [Coated] [dmFlag] NULL
ALTER TABLE [dbo].[Vessel] ADD [CoatedNote] [dmRemark] NULL
ALTER TABLE [dbo].[Vessel] ADD [Coiled] [dmFlag] NULL
ALTER TABLE [dbo].[Vessel] ADD [CoiledNote] [dmRemark] NULL
ALTER TABLE [dbo].[Vessel] ADD [Igs] [dmFlag] NULL
ALTER TABLE [dbo].[Vessel] ADD [Cow] [dmFlag] NULL
ALTER TABLE [dbo].[Vessel] ADD [Cls] [dmFlag] NULL
ALTER TABLE [dbo].[Vessel] ADD [Sbt] [dmFlag] NULL
ALTER TABLE [dbo].[Vessel] ADD [PumpsDisp] [dmDisp] NULL
ALTER TABLE [dbo].[Vessel] ADD [BowLoadDisch] [dmFlag] NULL
ALTER TABLE [dbo].[Vessel] ADD [SternLoadDisch] [dmFlag] NULL
ALTER TABLE [dbo].[Vessel] ADD [GasCubic] [int] NULL
ALTER TABLE [dbo].[Vessel] ADD [GasTemp] [smallint] NULL
ALTER TABLE [dbo].[Vessel] ADD [GasPressure] [real] NULL
ALTER TABLE [dbo].[Vessel] ADD [Ethylene] [dmFlag] NULL
ALTER TABLE [dbo].[Vessel] ADD [Vcm] [dmFlag] NULL
ALTER TABLE [dbo].[Vessel] ADD [Ammonia] [dmFlag] NULL
ALTER TABLE [dbo].[Vessel] ADD [Chemical] [dmFlag] NULL
ALTER TABLE [dbo].[Vessel] ADD [SpotPosition] [dmPosition] NULL
ALTER TABLE [dbo].[Vessel] ADD [SpotZone] [dmZoneCode] NULL
ALTER TABLE [dbo].[Vessel] ADD [SpotOpenDisp] [dmOpenDateText] NULL
ALTER TABLE [dbo].[Vessel] ADD [SpotOpenFrom] [datetime] NULL
ALTER TABLE [dbo].[Vessel] ADD [SpotOpenTo] [datetime] NULL
ALTER TABLE [dbo].[Vessel] ADD [SpotEntryDate] [datetime] NULL
ALTER TABLE [dbo].[Vessel] ADD [SpotEnteredBy] [dmUsrCode] NULL
ALTER TABLE [dbo].[Vessel] ADD [SpotSource] [dmOrgName] NULL
ALTER TABLE [dbo].[Vessel] ADD [LongPosition] [dmPosition] NULL
ALTER TABLE [dbo].[Vessel] ADD [LongZone] [dmZoneCode] NULL
ALTER TABLE [dbo].[Vessel] ADD [LongOpenDisp] [dmOpenDateText] NULL
ALTER TABLE [dbo].[Vessel] ADD [LongOpenFrom] [datetime] NULL
ALTER TABLE [dbo].[Vessel] ADD [LongOpenTo] [datetime] NULL
ALTER TABLE [dbo].[Vessel] ADD [LongEntryDate] [datetime] NULL
ALTER TABLE [dbo].[Vessel] ADD [LongEnteredBy] [dmUsrCode] NULL
ALTER TABLE [dbo].[Vessel] ADD [LongSource] [dmOrgName] NULL
ALTER TABLE [dbo].[Vessel] ADD [PositionNote] [dmRemark] NULL
ALTER TABLE [dbo].[Vessel] ADD [Contact1] [dmOrgName] NULL
ALTER TABLE [dbo].[Vessel] ADD [Contact2] [dmOrgName] NULL
ALTER TABLE [dbo].[Vessel] ADD [Contact3] [dmOrgName] NULL
ALTER TABLE [dbo].[Vessel] ADD [Direct] [dmFlag] NULL
SET ANSI_PADDING ON
ALTER TABLE [dbo].[Vessel] ADD [DispOwner] [varchar](60) NULL
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[Vessel] ADD [DispOwnerContact] [dmOrgName] NULL
ALTER TABLE [dbo].[Vessel] ADD [ContactToDisp] [tinyint] NULL
ALTER TABLE [dbo].[Vessel] ADD [Note] [dmMemo] NULL
ALTER TABLE [dbo].[Vessel] ADD [FeatureToDisp] [dmDisp] NULL
ALTER TABLE [dbo].[Vessel] ADD [DisplayContact] [dmOrgName] NULL
ALTER TABLE [dbo].[Vessel] ADD [SnpDisplayType] [dmVesselTypeCode] NOT NULL
ALTER TABLE [dbo].[Vessel] ADD [Checked] [tinyint] NULL
ALTER TABLE [dbo].[Vessel] ADD [CheckedBy] [dmUsrCode] NULL
ALTER TABLE [dbo].[Vessel] ADD [CheckedAt] [datetime] NULL
SET ANSI_PADDING ON
ALTER TABLE [dbo].[Vessel] ADD [SizeClass] [varchar](100) NULL
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[Vessel] ADD [BallastCapacity] [varchar](100) NULL
ALTER TABLE [dbo].[Vessel] ADD [CO2Fitted] [tinyint] NULL
/****** Object: Index [pkVessel] Script Date: 07/14/2009 12:59:18 ******/
ALTER TABLE [dbo].[Vessel] ADD CONSTRAINT [pkVessel] PRIMARY KEY NONCLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
I am also pasting below the common queries:
SELECT Vessel.ID [A4ID], /*VesselType.ForTank [A6ForTank],*/ Vessel.VesselType [A7VesselType], Vessel.Name [A9Name], Vessel.DisplayType
[A10DisplayType], Vessel.DwtHigh [A14DwtHigh], Vessel.DisplayType [A19DisplayType], Vessel.BuiltYear [A23BuiltYear]
FROM Vessel , VesselType
WHERE (Vessel.DwtHigh >= 50000)
AND(Vessel.DwtHigh = 1987)
AND(Vessel.BuiltYear = 20000)
AND(Vessel.DwtHigh <= 40000)
and VesselType.DisplayType=Vessel.DisplayType
and Vessel.ID = VAVesselData.Vessel
SELECT DISTINCT Vessel.ID [A4ID], VesselType.ForTank [A6ForTank], Vessel.VesselType [A7VesselType], Vessel.Name [A9Name], Vessel.DisplayType
[A10DisplayType], Vessel.Name [A14Name], Vessel.DisplayType [A18DisplayType], Vessel.AuxOilSeaCons [A21AuxOilSeaCons], Vessel.AuxOilSeaType
[A24AuxOilSeaType], Vessel.Bale [A27Bale], Vessel.Beam [A30Beam], Vessel.BulkHeads [A33BulkHeads], vVesselSpeedSrch.Capacity [A36Capacity],
vVesselSpeedSrch.Quality [A39Quality], vVesselSpeedSrch.Remark [A42Remark], vVesselSpeedSrch.CapUnit [A45CapUnit], Vessel.Cellular [A48Cellular],
Vessel.Class1 [A51Class1], Vessel.Coated [A54Coated], Vessel.Coiled [A58Coiled], Communications.email [A61email], Communications.Fax [A64Fax],
Communications.Master [A67Master], Communications.Telephone [A70Telephone], Communications.Telex [A73Telex], Vessel.YardCtry [A76YardCtry],
Vessel.Cow [A79Cow], Vessel.DoubleBottom [A82DoubleBottom], Vessel.DoubleHull [A85DoubleHull], Vessel.DoubleSide [A88DoubleSide], Vessel.DwtHigh
[A91DwtHigh], Vessel.EngBuilder [A94EngBuilder], Vessel.EngineMake [A97EngineMake], Exname.Name [A100Name], Vessel.Feu [A103Feu], Vessel.Flag [A106Flag],
Vessel.Fuelgrade [A109Fuelgrade], Vessel.GasCubic [A112GasCubic], Vessel.Note [A115Note], Vessel.Grain [A118Grain], Vessel.GTHigh [A121GTHigh],
Vessel.HaCover [A124HaCover], Vessel.HaDims [A127HaDims], Vessel.Hatches [A130Hatches], Vessel.Holds [A133Holds], Vessel.Homog14T [A136Homog14T],
Vessel.HvyStr [A139HvyStr], Vessel.Igs [A142Igs], Vessel.Tpc [A145Tpc], Vessel.LiquidCubic [A148LiquidCubic], Vessel.LloydsNumber [A151LloydsNumber],
Vessel.Loa [A154Loa], Vessel.NTHigh [A157NTHigh], Vessel.OreStr [A160OreStr], Vessel.Sbt [A163Sbt], Vessel.SSteelCubic [A166SSteelCubic],
Vessel.DraftHigh [A169DraftHigh], VAVesselData.VesselCode [A172VesselCode], Vessel.BuiltYear [A175BuiltYear]
FROM Vessel , VesselType , vVesselSpeedSrch ,
Communications , Exname , VAVesselData
WHERE (Vessel.Name like 'united%')AND(Vessel.Coated is NULL)
and VesselType.DisplayType=Vessel.DisplayType
and Vessel.ID*=vVesselSpeedSrch.Vessel
and Vessel.ID*=Communications.Vessel
and Vessel.ID *= Exname.Vessel
and Vessel.ID = VAVesselData.Vessel
SELECT Vessel.ID [A4ID], VesselType.ForTank [A6ForTank], Vessel.VesselType [A7VesselType], Vessel.Name [A9Name], Vessel.DisplayType
[A10DisplayType], vVesselHFSrch.Reference [A14Reference], Vessel.Name [A17Name], vVesselHFSrch.Status [A20Status], vVesselHFSrch.VMHeadType
[A26VMHeadType], Vessel.DwtHigh [A29DwtHigh], Vessel.DraftHigh [A32DraftHigh], Vessel.DisplayType [A35DisplayType], Vessel.BuiltYear
[A38BuiltYear], VesselSizeClass.SizeClass [A41SizeClass], Vessel.Grain [A44Grain], Vessel.Loa [A47Loa], Vessel.Beam [A50Beam]
FROM Vessel , VesselType , vVesselHFSrch , VesselSizeClass
WHERE (vVesselHFSrch.Status = 'Fixed'
OR vVesselHFSrch.Status = 'Running'
OR vVesselHFSrch.Status = 'Last Voyage Running')
and VesselType.DisplayType=Vessel.DisplayType
and Vessel.ID = vVesselHFSrch.ID
and ISNULL(Vessel.SizeClass,'') = ISNULL(VesselSizeClass.SizeClassDisp,'')
SELECT Vessel.ID [A4ID], VesselType.ForTank [A6ForTank], Vessel.VesselType [A7VesselType], Vessel.Name [A9Name], Vessel.DisplayType
[A10DisplayType], VAVesselData.VesselCode [A14VesselCode], Vessel.Name [A17Name], vVesselHFSrch.Status [A20Status], vVesselHFSrch.VMHeadType
[A26VMHeadType], Vessel.GearDisp [A29GearDisp], Vessel.DwtHigh [A31DwtHigh], VAVesselData.WinterDwt [A34WinterDwt], Vessel.DraftHigh
[A37DraftHigh], VAVesselData.TropicSaltDraft [A40TropicSaltDraft], VAVesselData.WinterSaltDraft [A43WinterSaltDraft], Vessel.DisplayType
[A46DisplayType], Vessel.BuiltYear [A49BuiltYear], VesselSizeClass.SizeClass [A52SizeClass], Vessel.Grain [A55Grain], Vessel.Loa [A58Loa],
Vessel.Beam [A61Beam]
FROM Vessel , VesselType , VAVesselData , vVesselHFSrch , VesselSizeClass
WHERE (vVesselHFSrch.Status = 'Fixed'
OR vVesselHFSrch.Status = 'Running'
OR vVesselHFSrch.Status = 'Last Voyage Running')
and VesselType.DisplayType=Vessel.DisplayType and Vessel.ID = VAVesselData.Vessel
and Vessel.ID = vVesselHFSrch.ID
and ISNULL(Vessel.SizeClass,'') = ISNULL(VesselSizeClass.SizeClassDisp,'')
order by Vessel.DwtHigh desc
SELECT Vessel.ID [A4ID], VesselType.ForTank [A6ForTank], Vessel.VesselType [A7VesselType], Vessel.Name [A9Name], Vessel.DisplayType
[A10DisplayType], Vessel.Name [A14Name], Vessel.DisplayType [A32DisplayType], vVesselSubTypeSrch.SubTypeFullName [A35SubTypeFullName],
VesselSizeClass.SizeClass [A38SizeClass], VAVesselData.VesselCode [A41VesselCode], Vessel.DryDockDates [A44DryDockDates], Vessel.SurveyDates
[A47SurveyDates], Vessel.DwtHigh [A50DwtHigh], Vessel.GTHigh [A53GTHigh], Vessel.NTHigh [A56NTHigh], vVesselHFSrch.VMHeadType [A59VMHeadType],
vVesselHFSrch.Reference [A63Reference], vVesselHFSrch.Status [A64Status]
FROM Vessel , VesselType , vVesselSubTypeSrch , VesselSizeClass ,
VAVesselData , vVesselHFSrch
WHERE (Vessel.Name 'Apache Maiden')AND(Vessel.Name 'Cherokee Princess')AND(Vessel.Name 'Inca Maiden')
AND(Vessel.Name 'Jadran')AND(Vessel.Name 'Kickapoo Belle')AND(Vessel.Name 'Kiowa Princess')AND(Vessel.Name 'Navajo Princess')
AND(Vessel.Name 'Peljesac')AND(Vessel.Name 'Seneca Maiden')AND(Vessel.Name 'Pacific Bangbin')AND(Vessel.Name 'Pacific Bangcheng')
AND(Vessel.Name 'Pacific Bangpu')AND(Vessel.Name 'Addu Star')AND(Vessel.Name 'Shan King')AND(vVesselHFSrch.VMHeadType = 'Owned'
OR vVesselHFSrch.VMHeadType = 'BB')AND(vVesselHFSrch.Status = 'Running' OR vVesselHFSrch.Status = 'Fixed'
OR vVesselHFSrch.Status = 'Last Voyage Running')
and VesselType.DisplayType=Vessel.DisplayType
and Vessel.ID *= vVesselSubTypeSrch.ID
and ISNULL(Vessel.SizeClass,'') = ISNULL(VesselSizeClass.SizeClassDisp,'')
and Vessel.ID = VAVesselData.Vessel
and Vessel.ID = vVesselHFSrch.ID
order by Vessel.Name
My proposed indexes:
CREATE CLUSTERED INDEX iskVesselDwt ON Vessel(DwtHigh, BuiltYear)
GO
--NC
CREATE UNIQUE NONCLUSTERED INDEX iskVesselIdDispTypeVesselTypeNameTest ON Vessel (ID, DisplayType, Name, VesselType)
GO
CREATE UNIQUE NONCLUSTERED INDEX iskVAVesselDataVesselCodeTest ON VAVesselData(Vessel, VesselCode)
GO
CREATE NONCLUSTERED INDEX iskVesselHomog14TTest ON Vessel (Homog14T, ID, DisplayType, Name, VesselType)
GO
CREATE NONCLUSTERED INDEX iskVesselLloydsNoTest ON Vessel (LloydsNumber, ID, DisplayType, Name, VesselType)
GO
CREATE NONCLUSTERED INDEX iskVesselTeuTest ON Vessel (Teu, ID, DisplayType, Name, VesselType)
GO
CREATE NONCLUSTERED INDEX iskVesselFlagSubTypeLoaBeamTest ON Vessel (Flag, SubTypeDisp, Loa, Beam, ID, DisplayType, Name, VesselType)
GO
July 14, 2009 at 5:32 am
Hi ,
Ok first thing is first. I would definitly try and rewrite all that code to fit the ansi standard. This is a very old style of writing SQL and is hard to read.
I've managed to look at the vessel table only so far and I think this might help as a start.
CLUSTERED ON ID
IX_1 DisplayType,BuildtYear,DwtHight INCLUDE (VesselType,Name)
IX_2 DisplayType,Coated,Name,SizeClass
WE might need to add some include columns to the IX_2 index cause I have a feel there will be some lookup's in the query plans of query 4.
Without being able to see the execution plans with these indexes it's hard to see what to change.
Here are some very good article to that might help you decide which indexes to use.
http://sqlinthewild.co.za/index.php/category/sql-server/indexes/
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
July 14, 2009 at 5:50 am
Hi,
Apologies about the old code. We are still supporting sql2000 and i would appreciate it if we create the indexes in a format that is also sql2000 compatible. I see that the indexes you have just given are in sql2005 format.
July 14, 2009 at 6:02 am
if you remove the include from the index it will work for 2000, but then it would cover the full query for output fields
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
July 14, 2009 at 6:20 am
Hi Christopher,
I would like to start by thanking for taking your time to have a look.
I have a few question though regarding the choices you have made. I feel that with these choices we will be able to discuss the issues that are of concern to me.
1. I see that you chose ID as the custering key even though from the few queries i have given you it's clear that we are using DwtHigh and BuiltYear for range searches. I would then say these two columns are better candidates for our clustering key.
2. I do agree with you that VesselType and Name are always in every query hence the INCLUDE statement in IX_1. However, since we included these two cols. in IX_1 i would also say we should include ID and DisplayType aswell since these two columns are always retrieved in every search.
3. Generally, how would you design your indexes to handle a case where you have columns that are always retrieved, even when they are not in the search predicate (where clause), without using the INCLUDE statement.
Thank you.
July 14, 2009 at 6:28 am
I'm happy to try and help as best I can.
Ok so the reason I select ID as the clustering key is because it seems to be used in all your join clauses(I think) I really do find non ansi code hard to read.
The order of the indexes is very important due to the fact that you have some in-equality clauses in your where clause.
I would say that if a column is always returned in every query and the column size is not too big then it should be ok to have it in an index.
normally what I do is make the indexes cover as best they can firstly for the where clause, then I test my queries for performance and update the indexes buy either widening what I have or by adding new indexes.
That's normally the approach that I take, and focus on the top 5 slowest/most used queries.
Hope this helps.
The thing to remember is that Indexes are not the only thing that affect your performances. It's also the query itself. So I always start by making sure I think the query is written in the best possible way , before I start diving into indexes.
Because by removing indexes you may actual cause more problems than you expect.
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
July 14, 2009 at 6:36 am
Thank you very much.
I would say you have assisted me a lot. I will re-consider my indexes. I am also going to come up with the bench mark queries as you suggested to use in my test.
July 14, 2009 at 6:38 am
Also I do really really recommend going through the index posts on Gail's blog. She is brilliant when it comes to perf tuning and indexes. I learn't a lot of what I know from speaking to her and reading her blog. And of coarse from reading and trying to help people on SSC.
good luck and come back with more questions if you have 🙂
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply