Non-Clustered Indexes Query Performance

Question

Post reply

Non-Clustered Indexes Query Performance

mha_hamed

SSC Enthusiast

Points: 111
More actions
September 7, 2009 at 9:48 am

#209743

Hi,
I've a table
Employee
Id INT,
FirstName VARCHAR(10),
MiddleName VARCHAR(10),
LastName VARCHAR(10),
DOB DATETIME
Which has 5,000,000 rows. I want to get the DOB of a person whose FirstName = 'A', MiddleName = 'B' and LastName = 'C'
SELECT DOB from Employee
WHERE FirstName = 'A' AND MiddleName = 'B' ANd LastName = 'C'
How do I improve the peformance?
1. Should I create individual Non-Clustered indexes for FirstName, MiddleName and LastName
or
2. Should I create composite "Unique" Non-Clustered index comprising FirstName, MiddleName and LastName
or
3. Should I create a Non-Clustered index on FirstName and Include columns MiddleName and LastName
Things in mind, Composite indexes might increase the space, if we are including all the three columns having separate or composite matters.
Note: I'm taking care of the order of the columns as well.
Please advice, what should be best strategy here?

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

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

Marcin Gol Hall of Fame Points: 3314 More actions · Answer 1

can you show us your clustered index ? if the key is Id - maybe it shouldn't - it depends on your queries

i think that you should create following index:

create nonclustered/clustered index MyGreatIndex on Schema.MyGreatTable (LastName, FirstName, MiddleName) include (DOB)

mha_hamed SSC Enthusiast Points: 111 More actions · Answer 2

Thanks a lot! Marcin for replying

(the table I projected here is just a sample table which is very much close to my real table)

Id is my Clustered Index as I'm using it for inner join with another table.

Ok, as you said that I can probably go with non-clustered index on (LastName, FirstName, MiddleName) include (DOB)

Having composite non-clustered index on the three columns will that be an issue in terms of space?

Please give your concluding comments, so that I jump start on changing my table

Thanks again!

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

If you add 3 indexes each on a single column, SQL will likely use only one of them. There is something called index intersection, where SQL will seek on multiple indexes and intersect the results, but that's very rare.

So with 3 indexes, one on firstname, one on lastname and one on middlename, SQL will seek on one of them (the one that returns fewest rows for the query), then do a lookup to the cluster to get the other columns needed, then filter on the remaining two conditions before returning the data.

If you create an index on FirstName and Include columns MiddleName and LastName, then SQL can seek only on the firstname column, then going through the rows that match, it will filter out the ones that don't match on MiddleName and LastName, then it will do a lookup to the cluster to fetch the DOB column before returning the data.

If you create an index on FirstName, MiddleName and LastName, then SQL can do a single seek on the three predicates, get to exactly the matching rows in one go, then lookup to the cluster to get the DOB.

If there are few people with the same FirstName, MiddleName and LastName, I probably wouldn't make the index covering by including the DOB column. The index is already pretty large. Depends on the cost of that lookup though.

Yes, the index is pretty big, it will increase the space used in the DB. Standard tradeoff, space for speed.

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

Vijay Mishra SSChasing Mays Points: 647 More actions · Answer 4

If I were you my plan would be something like this; get the randomly selected sample data and see which of the following is more unique (1 - (dup_val/ sample record count))

1. first_nm

2. last_nm

3. first_nm and last_nm

Obviously you will say 3 option will be more unique, but if there is not much difference between the (1 opt and 3 opt) or (2 opt and 3 opt). I would have opted either of the first 2.

This way you can limit you lookup operations with out sacrificing much on space

Thanks

-Vijay

Best Regards,

Vijay Mishra

Marcin Gol Hall of Fame Points: 3314 More actions · Answer 5

Marcin Gol

Hall of Fame

Points: 3314

September 8, 2009 at 10:51 am

#1050649

GilaMonster - good point!

mha_hamed SSC Enthusiast Points: 111 More actions · Answer 6

Thanks Gail Shaw, MarcinGol and Vijay Mishra for replying and giving an insight of index performance to me.