Post reply

Help me in creating indexes

  • June 6, 2010 at 11:41 pm

    #387088

    The following query is taking too long and i need some help in tuning the query

    Query:

    select top(1) t2.char_value

    from table1 t1

    inner join

    table2 t2

    ON t2.value_recognizer = t1.recognizer

    WHERE t1.nme = 'test'

    AND t2.value_nme = 'destinity'

    DDL of the tables:

    CREATE TABLE [dbo].[table1](

    [recognizer] [int] IDENTITY(1,1) NOT NULL,

    [nme] [varchar](75) NOT NULL,

    [descript] [varchar](250) NOT NULL,

    CONSTRAINT [table1_pk] PRIMARY KEY CLUSTERED

    (

    [recognizer] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    CREATE TABLE [dbo].[table2](

    [value_recognizer] [int] IDENTITY(1,1) NOT NULL,

    [recognizer] [int] NOT NULL,

    [char_value] [varchar](50) NOT NULL,

    [default_value_indicator] [char](1) NULL,

    [value_nme] [varchar](75) NOT NULL,

    [desc] [varchar](250) NOT NULL,

    CONSTRAINT [table2_pk] PRIMARY KEY CLUSTERED

    (

    [value_recognizer] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

  • June 6, 2010 at 11:42 pm

    #1176938

    Right now i have only clustered indexes on the primary keys

  • June 7, 2010 at 1:47 am

    #1176954

    Hi could you please try the below steps and see whether it is improving the speed.

    1. Make the PRIMARY KEY on the table2 as NON Clustered

    2. Create a Clustered index on table2 for the column recognizer

    3. Creta a non clustered index on table2 for the column value_nme

    Thanks & Regards,
    MC

  • June 7, 2010 at 5:32 am

    #1177022

    Please post execution plan, as per http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 7, 2010 at 5:47 am

    #1177029

    Please find the attachment for execution plan

  • June 7, 2010 at 3:02 pm

    #1177233

    Indexes should be looked at in a whole application perspective to get the best setup. Trying to optimize for this specific query could lead to a less than optimal index when taking a look at the DB as a whole picture. That being said, I would try creating an index on table1 for nme including recognizer. On table2 index recognizer, value_nme and include char_value.

  • June 10, 2010 at 12:12 am

    #1178451

    cfradenburg (6/7/2010)

    I would try creating an index on table1 for nme including recognizer. On table2 index recognizer, value_nme and include char_value.

    i do agree with it

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 7 posts - 1 through 6 (of 6 total)

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