Hello

I have this query:

SELECT *
FROM sample
   INNER JOIN test ON sample.sample_number = test.sample_number
   INNER JOIN result ON test.test_number = result.test_number
   WHERE sampled_date BETWEEN '2010-03-17 09:00' AND '2010-03-17 12:00'

the biggest table here is RESULT, contains 11.1M records. The left 2 tables about 1M.

this query works slowly (more than 10 minutes) and returns about 800 records. executing plan shows clustered index scan (over it's PRIMARY KEY (result.result_number, which actually doesn't take part in query)) over all 11M records. RESULT.TEST_NUMBER is a clustered primary key.

if I change 2010-03-17 09:00 to 2010-03-17 10:00 - i get about 40 records. it executes for 300ms. and plan shows index seek (over result.test_number index)

if i replace * in SELECT clause to result.test_number (covered with index) - then all become fast in first case too. this points to hdd IO issues, but doesn't clarifies changing plan.

so, any ideas?

UPDATE: sampled_date is in table sample and covered by index. other fields from this query: test.sample_number is covered by index and result.test_number too.

UPDATE 2: obviously than sql server in any reasons don't want to use index.

i did a small experiment: i remove INNER JOIN with result, select all test.test_number and after that do

SELECT * FROM RESULT WHERE TEST_NUMBER IN (...)

this, of course, works fast. but i cannot get what is the difference and why query optimizer choose such inappropriate way to select data in 1st case.