Index Planning

  • 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.:-)

  • 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]

    SQL-4-Life
  • 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

  • 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]

    SQL-4-Life
  • 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.

  • 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]

    SQL-4-Life
  • 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.

  • 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]

    SQL-4-Life
  • 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.

  • 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]

    SQL-4-Life

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply