Help me in creating indexes

Question

Post reply

Help me in creating indexes

sandyinfowave

Default port

Points: 1490
More actions
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]

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

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

sandyinfowave Default port Points: 1490 More actions · Answer 1

Right now i have only clustered indexes on the primary keys

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 2

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Please post execution plan, as per http://www.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

sandyinfowave Default port Points: 1490 More actions · Answer 4

sandyinfowave

Default port

Points: 1490

June 7, 2010 at 5:47 am

#1177029

Please find the attachment for execution plan

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 5

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.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 6

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;-)