SQL plan not using the index - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL plan not using the index

Author

Topic

EWDBA

2 Posts

Posted - 2006-03-08 : 14:17:41

Hi all, I have been running into this same issue for some time now and can't come up with a valid reason why SQL is doing what s is doing...
In a nut shell I have three tables
t1 is a fact table
t2 is a time dimension table
t3 is a parameter table that get loaded prior to the query being executed.

Simple code


Select count (t1.stuff) 
From t1
Inner join t2
On
t1.date_key  = t2.date_key 
Inner join t3
on
t2.date_number between t3.start_date and t3.end_date

The optimizer performs a full table scan on the fact then applies the date range filter... Though if I were to replace the start_date and end_date values with hardcode values then the optimizer uses the index and pulls only those records needed from the fact.

Really weird...

Any thoughts?
Thanks
Eric

activecrypt
Posting Yak Master

165 Posts

Posted - 2006-03-08 : 22:12:25

Hi,
would you post table structure , index and some sample data ?

Regards

Andy Davis
Sql Shield Team
--------------------------------------------
SQL Server Encryption Software
http://www.sql-shield.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources