index - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

index

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-12-21 : 09:56:25

Hi,
The table tblMain has several fields.
A number of the fields are added to the primary key as follows:the following keys:

CONSTRAINT [PK_tblMain] PRIMARY KEY CLUSTERED
(
[MyVDate] ASC,
[MyIID] ASC,
[MyRSeq] ASC,
[MyC] ASC

The following sql needs to be optimized.

Select MAX(MyVDate) from tblMain where MyRSeq=0 and MyIID=22 and MyVDate < '2011-12-20'

Do you think I should add indexes for MyVDate only?

What do you think please?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-21 : 12:13:54

what does current execution plan suggest?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-21 : 13:34:16

nope your key is fine

a lot depends on the cardinality of the data thoiugh

How many rows in the table

Post the Table DDL

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-12-21 : 13:57:40

If you are really looking for speed create an indexed view.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-12-21 : 15:49:26

The pk is OK for that query, but it's not the best. The key columns are in the wrong order to seek optimally. Try a nonclustered index on (MyRSeq, MyIID, MyVDate). That'll be the fastest possible for that query.

p.s. A 4-column clustered index is likely not idea. Why are those 4 the cluster?

--
Gail Shaw
SQL Server MVP

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-21 : 15:52:23

What Gail means is that a date as the first column might force a scan

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-12-21 : 16:21:13

No, that's not what I mean. It will seek.

The leading column is being used as an inequality. That means that only the inequality can be done via an index seek, the other two predicates have to be applied as secondary filters. With the nonclustered index I suggested (the date as the 3rd column), SQL can do both equality predicates and the inequality as a single seek operation.

--
Gail Shaw
SQL Server MVP

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-21 : 16:31:07

quote:
Originally posted by X002548

What Gail means is that a date as the first column might force a scan

Where'd you come up with that?

Kimberly Tripp's favorite clustered index is DateTimeColumn,IdentityColumn, with IdentityColumn as PK and non-clustered.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources