SQL query slow when joining with a sub query and only happened for one big Database

All my complex stored procedure works instantly for most of DBs,

But it will take more than 30 sec to execute them on DB X, maybe more but we didn't find yet.

DB X doesn't have the most data but our supporter 'delete' some d开发者_StackOverflow中文版ata and re-insert them recently.

I've corrected the table identity Index but it doesn't help.

Then I found when a light table 'Left Join' a sub query which will return the main data, the execution becomes slow.

The sub query itself is quick and also If I insert sub query to a temp hash table and left join the hash table, the query is fast!

Anyone know what happened to this DB X, and the solution ?

I found it caused by missing index, but I don't understand how come now? I also worried about the speed for inserting a big amount data if set index on server.

Have statistics been updated and indexes been rebuilt? Or disabled?

Especially after a lot of inserts, deletes etc

Thanks

I found it caused by missing index, but I don't understand how come now? I also worried about the speed for inserting a big amount data if set index on server.