Slow query with unexpected index scan
indexing, performance, sql
Solution
A couple things to try:
- Update statistics
- Add hints to the query about what index to use (in SQL Server you might say `WITH (INDEX(myindex))` after specifying a table)
EDIT: You noted that copying the database made it work, which tells me that the index statistics were out of date. You can update them with something like `UPDATE STATISTICS mytable` on a regular basis.
Use `EXEC sp_updatestats` to update the whole database.
Problem
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. UPDATE 3: after backing up database and restoring to database with new name - both requests work fast as expected even on much more ranges... so - are there any special commands to clean or optimize, whatever, that can be relevant to this? :-(