Query Optimization

Question

Post reply

Query Optimization

Jpotucek

SSCoach

Points: 18583
More actions
April 3, 2012 at 10:23 am

#254258

When I run this Select statement through the Estimated Execution Plan Wizard
SELECT title, summary, id, author, subarea FROM Article WHERE path in ('$specialColumn1', '$specialColumn2') ORDER BY subArea
It is doing Clustered Index Seek on PK_Article (the primary Key)
I've provided the DDL for the table and some sample data I am wondering if anyone has any suggestions to optomize this query!!!!????
Create table dbo.Art
( id int identity,
IW_State VARCHAR(255) not null,
path VARCHAR(255) not null,
contentArea CHAR(10) not null,
homepage CHAR(5) null,
title NVARCHAR(400) null,
summary NVARCHAR(1000) null,
keywords NVARCHAR(50) not null,
author NVARCHAR(50) null,
type CHAR(10) not null,
subArea CHAR(10) null,
publishDate datetime not null,
expireDate datetime not null,
articleLanguage CHAR(5) not null,
indexImage VARCHAR(255) null,
eventStartDate datetime null,
eventEndDate datetime null,
eventLocation NVARCHAR(50) null,
agentID CHAR(10) null,
ccText ntext null,
indexImageCaption NVARCHAR(100) null) ;
create Index IX_Art_1
on dbo.Art
(id)
With FillFactor=90
On [PRIMARY]
Where <filter>;
create Index IX_Art_2
on dbo.Art
(contentArea, expireDate)
With FillFactor=90
On [PRIMARY]
Where <filter>;
alter table dbo.Art
add constraint PK_Art
primary key (path);
title summary idauthorsubarea
Special UpdatesSpecial news to Agent Users.548008EVENT04
test title test summary 548003meAUTH01
test title test summary 548004meAUTH01
test title test summary 548002meAUTH01

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Looks like it is doing just what you need. It is doing an index seek on the primary key.

What do you think is wrong with the query?

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

You're unlikely to get much more optimal than a seek on the clustered index.

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

Jpotucek SSCoach Points: 18583 More actions · Answer 3

Thank you. one of the devopers had asked advice on how to optomize it.. i just thought maybe i was missing something... thanks!!