we running the stored procedure on a daily basis to perform the some activity. we have used the more temp table (# tables) and indexes on temp table.

i deployed the procedure and ran it completed successfully in 1 hour. If i run the same procedure for same data without modifying anything, it takling 5 hours to complete.

If i drop the procedure and recreate it and run it is taking 1 hour only.

I facing the problem for every run of the procedure after the first run. It creating the performance issue. Please share your thoughts on this.