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 |
|
chrianth
Yak Posting Veteran
50 Posts |
 Posted - 2009-01-22 : 01:47:10
|
| Hi Guys,For example I have tables with indexes like on the example below.table_1 (index = table_1_effDate ; index keys = effDate)table_2 (index = table_2_effPK ; index keys = PK)if I query like the one below....select * from table_1 a with(index = table_1_effDate)join table_2 bon (a.ID = b.t1_ID)where a.effDate = '2009-10-01'will there be any difference in performance if I remove the code below on the query...?with(index = table_1_effDate)or is it even better to use the table_2 index...?Thanks. |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-01-22 : 02:45:49
|
| Index hints are generally not a good idea. It means that you are crippling the optimiser by telling it what it has to do. If that isn't the most efficient index that you've hinted, then you will get a performance improvement by removing it.Generally, hint indexes when you are 100% sure you know better than the optimiser, you know why it's not picking the indexes you want and you know that the index you've forced is better. Even then, retest every few months as things do change.--Gail ShawSQL Server MVP |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-22 : 09:41:52
|
| [code]select * from table_1 a with(index = table_1_effDate)join table_2 bon (a.ID = b.t1_ID)where a.effDate = '2009-10-01'[/code]Also when you are doing this you are unnecessary fetching ID col from both tables. Try replacing * with columns you want instead. |
|
|
|
chrianth
Yak Posting Veteran
50 Posts |
Posted - 2009-01-22 : 21:42:02
|
quote:
Originally posted by GilaMonster
Index hints are generally not a good idea. It means that you are crippling the optimiser by telling it what it has to do. If that isn't the most efficient index that you've hinted, then you will get a performance improvement by removing it.Generally, hint indexes when you are 100% sure you know better than the optimiser, you know why it's not picking the indexes you want and you know that the index you've forced is better. Even then, retest every few months as things do change.--Gail ShawSQL Server MVP
If I have... table_1 indexes table_1_effDate ; index keys = effDate table_1_ID ; index keys = IDHow do I know the effecient index to use?table structure is .....ID | effDate | etc.......9001 | 2009-01-25 00:00:00.000 | etc.......8002 | 2009-01-25 00:00:00.000 | etc.......2003 | 2009-01-26 00:00:00.000 | etc.......3404 | 2009-01-27 00:00:00.000 | etc.......1705 | 2009-01-25 00:00:00.000 | etc.......7606 | 2009-01-25 00:00:00.000 | etc.......8907 | 2009-01-25 00:00:00.000 | etc.......2308 | 2009-01-25 00:00:00.000 | etc.......8709 | 2009-01-25 00:00:00.000 | etc.......Thanks. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-01-23 : 02:34:14
|
quote:
Originally posted by chrianth
How do I know the effecient index to use?
Don't. Leave that up to the optimiser. There's no one optimal index, it will chance as the amount and distribution of the data in the table changes.As I said before, unless you are 100% sure that you know better than the query optimiser, leave the hints off.--Gail ShawSQL Server MVP |
|
|
|
|
|
|
|
|
Indexes question