Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Non-Clustered Indexes Query Performance Expand / Collapse
Author
Message
Posted Monday, September 07, 2009 9:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 13, 2010 7:02 AM
Points: 5, Visits: 41
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?



Post #783840
Posted Monday, September 07, 2009 11:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, October 03, 2010 3:50 AM
Points: 146, Visits: 178
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)
Post #783874
Posted Monday, September 07, 2009 11:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 13, 2010 7:02 AM
Points: 5, Visits: 41
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!
Post #783991
Posted Tuesday, September 08, 2009 12:53 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:42 AM
Points: 41,547, Visits: 34,469
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 2008, MVP
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

Post #784020
Posted Tuesday, September 08, 2009 6:57 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 12:12 AM
Points: 414, Visits: 875
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



Post #784204
Posted Tuesday, September 08, 2009 10:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, October 03, 2010 3:50 AM
Points: 146, Visits: 178
GilaMonster - good point!
Post #784403
Posted Tuesday, September 08, 2009 10:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 13, 2010 7:02 AM
Points: 5, Visits: 41
Thanks Gail Shaw, MarcinGol and Vijay Mishra for replying and giving an insight of index performance to me.
Post #784702
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse