>>Well, if you include these fields in the index, the query will perform better but your DML operations will take more time.
>
>I have found that this is related to a where clause at the end which is from a inner join table. If I remove it, the result is instant. That inner join table only has 388 records. So, if I include that condition, it takes over 60 seconds compare to instant result if I do not have that condition. I will try about changing the order of the inner join lines. Unless you have an idea of what is going on.
If you post the query (or sample) it will be easier to discuss. Take also a look at this article, may be applying LOOP hint can help
http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/use-a-outer-loop-join-for-faster-perform
If it's not broken, fix it until it is.
My Blog