February 4, 2004 at 9:57 am
I'm hoping someone can help answer this for me. I have two tables that have a date column that each have an index on. When i use the query analyzer for table A it shows that it is using the index (index seek) when I run:
select * from table a where date > '01/01/04'
However, when I run the same query on table B, I get a table scan, but when I do the this query:
select * from table b where date = '01/01/04' it shows an index scan on the execution plan.
Shouldn't they both give me an index scan when executed. The only difference between the tables is the number of rows. Table B is much larger than table A.
Any suggestions would be greatly appreciated.
J.D.
February 4, 2004 at 12:35 pm
If the optimizer thinks that a large number of rows will be returned, then it may force a table scan.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 4, 2004 at 1:16 pm
Thanks Steve. Doesn't that defeat the purpose of having an index? The results may be a large number of rows, is there a way around this?
J.D.
February 4, 2004 at 5:34 pm
Is the index sorted?
February 4, 2004 at 5:43 pm
It looks like tableB doesn't have same indexes as tableA.
You can force the query to use a specific index by using index hint but It may not give you better peformance than the execution plan SQL Server generates without the hint.
February 5, 2004 at 8:53 am
Here's something interesting (at least for me), when I changed the date from 01/01/04 to 02/04/04, the execution plan shows the index being used.
February 9, 2004 at 2:55 am
In this case I would suggest to you to reindex and/or update the statistics.
You could also check how many rows you have for 01/01/04 and how many for 02/04/04
Bye
Gabor
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply