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 |
|
pwcphoto
Yak Posting Veteran
69 Posts |
 Posted - 2005-04-14 : 02:25:41
|
| Is there a way in a stored procedure to specify, which index it is to use or does SQLserver 2000 choose automatically based on all those available for the particular table?I have a table with about 400,000 records. When I do an order by using the ID column DESC it takes a fraction of a second. When I do an order by on the other column it takes about 11 seconds. The column is an int type. In built an index for it but didn't seem to make much difference.PhilI used to be a rocket scientist. Now I am just a space cadet... |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2005-04-14 : 06:16:20
|
| Here is a decent article on using table hints and index hints.http://www.sql-server-performance.com/rd_table_hints.aspNow that I gave you some info, I'm going to caution you against doing this. Most likely you will hurt the overall performance of your stored procedure by forcing sql to do something it doesn't believe is efficient. You might force it to use a particular index to use for the order by and at the same time cause your where clause to tank, thus causing you to break even or worse, experience a net loss on your performance. You might want to take a look at your overall index structure on the table via explain plan. Sometimes creating a composite index (i.e. your where clause column and order column) is sufficient to give you what you need. Please before you go trying to out-think the optimizer you should read up on indexing and different strategies. Often it's not optimizer that makes the mistake it's the developer who needs to write the index well so that optimizer makes the write choice.Mike"oh, that monkey is going to pay" |
 |
|
|
|
|
|
Using Indexes