I noticed a stored procedure in SQL Server (2008) -- let's call it p_TheProcedure -- taking about 10 seconds to run on production, Since this is a procedure that generally should run in under a second, that was a problem that I needed to fix.
So I created a copy of called p_TheProcedure_test that I could use to fine-tune without affecting anything on the live site. (Before you lecture me on testing things on production, let me just point out that this procedure doesn't update any tables other than temp tables). So I ran p_TheProcedure_test and noticed that it ran in under 1 second. That's funny, let me try p_TheProcedure again -- 10 seconds. What's going on? The code is exactly the same in both procedures.
So, I tried this:
drop procedure dbo.p_TheProcedure
go
create procedure dbo.p_TheProcedure
...
After than, running dbo.p_TheProcedure is back down to below 1 second. Problem solved.
I'm guessing this has to do with a bad query execution plan. I could research the exact technical reason, but I'm not a theoretician, and I have work to do.
