| Author |
Topic |
|
lfmn
Posting Yak Master
141 Posts |
 Posted - 2003-01-28 : 12:14:17
|
| I know I've seen this somewhere, but can't find it. I'm sure I read that if a table has under a certain number of rows, it uses a table scan regardless of whether or not the table has an index because it's more efficient. If anyone know the anwer to this, it will save me much head scratching!SQL is useful if you don't know cursors :-) |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-01-28 : 13:17:40
|
| I may be wrong but I don't think there is a number of rows involved.The optimiser will take the plan with the minimum number of page accesses to get the data.If the index is covering then it should always use the index.If the table is small and you are accessing data not in the index then it may be that reading the index pages will cause more reads.The optimiser may also get it wrong.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
lfmn
Posting Yak Master
141 Posts |
Posted - 2003-01-28 : 13:29:00
|
| According to SQL Server 7.0 DBA Survival Guide page 590, you should not index tables with a small number of rows. I guess I was looking for something more specific. We have tables with 20 million rows. I guess I'm thinking anything under 10,000 is small.SQL is useful if you don't know cursors :-) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-01-28 : 13:31:08
|
| Doesn't it also check the density and if the density isn't low enough then it does a table scan? Density relates to number of records in the table and number of distinct values in the first column of the index. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-01-28 : 13:42:43
|
| I know I had a case where I had a table of 5 lookup records, and a table of 100,000 transactions. The lookup table had an index.When I wrote a simple INNER JOIN between the two tables, and SQL wanted to use an index on the small lookup table so it sorted the entire 100,000 transactions in the same order as the lookup table so it could do this. This was very slow.Then, when I used the INDEX(0) hint, SQL just did a table scan on the small lookup table and the query execute something like 100 times faster because it didn't have to first sort 100,000 records.- Jeff |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-01-28 : 13:50:03
|
| I am very surprised that SQL used the index on a table with five records. I have never seen that happen. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-01-28 : 13:57:12
|
quote:
The optimiser may also get it wrong.
==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-01-28 : 14:14:02
|
| I noticed that this was fine in SQL 2000 -- the original problem I had on certain Views was in 7.0. After I upgraded, I took out all of my optimizer hints and SQL seems to be doing a much better job in the new version.- Jeff |
|
|
|
|
When does an indexed table use a table scan?