When sql queries running very slower we can consider forcing an index using WITH INDEX command
Syntax: Select * from <tablename> with (index(<index name>))
This we found very useful in one of our project where we were faced performance issues. Our code is basically querying a table with date condition.
select @st_dt = max(field1) from table1 with (index (index_nm)) where field1 <= @start_dt
The date value is passed through sql variable. The query is running for ever even though it has the index on field1. We tried to run the query using WITH INDEX by mentioning the index name on the field. This query ran just like that in few minutes.
You can think of using forced index when you pass some values in where clause thru variables and only the indexed fields are used in the where clause.
Let me know your thoughts on this.