>I would suggest that the live db server is using a different execution plan; compare them. I've had it happen to me.
It might be something like that.
I also switched that query into the Database Engine Tuning Advisor. It recommends 20 indexes, where all of them would be combined indexes. It estimates that it could improve up to 77%. That would still be not enough compare to the time it takes right now to have it executed instantly on our test environment.
I am looking at other potential issues right now. Based on the above info from the Database Engine Tuning Advisor, do you see any other things in regards to that I should look at?