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.
| Author |
Topic |
|
delpi767
Starting Member
11 Posts |
 Posted - 2009-02-06 : 23:21:28
|
| I have a table with 4,500,000 dummy records.Each record contains fields entitledLastNameFirstNameMaidenNameObviously not all rows have a value for maidenname.When I say select * from members where lastname = 'ODOM'Query analyzer returns 74 rows in 1 secondWhen I say When I say select * from members where maidenname = 'ODOM'Query analyzer takes over two minutes to return 34 rows.Can someone explain this to me and offer a solution? Thanks,Mac |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
delpi767
Starting Member
11 Posts |
Posted - 2009-02-07 : 08:24:35
|
| Actually there are no indexes except on the primary key which is an identity column. Both fields are char (26). However, I'll give the index a try.MacRegards,-dmd- |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-07 : 08:53:28
|
| add a non clustered index on maidenname and check. Also its better to add the index only if you're sure that frequency of searching the table for maidenname value is high |
|
|
|
delpi767
Starting Member
11 Posts |
Posted - 2009-02-07 : 11:50:38
|
| I've now added an index for maidenHowever, last name still returns its rows almost instantly (not indexed) while maiden still take 10-20 seconds.Any thoughts?Thanks,Mac |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-07 : 11:54:59
|
| what does execution plan suggests in both cases? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
querying