Can someone please explain why this works. Here is the scenerio. I have a stored proc and it starts to run slow. Then I pick a parameter and declare a variable to house its value and in the proc use the declared variable instead of the parameter. The proc will then speed up considerably.
I think it has something to do with the cached plan and statistics, but I am not sure. Is it that statistics get out of date as the database grows and changes so that the cached plan is optimized on a past state of the data which is different from the present state of the data?
thanks.
What you describe is commonly referred to as parameter sniffing, and it seems to be a SQL Server only issue - never had it on Oracle IME, nor am I aware of the issue on MySQL. The link I gave breaks down the issue well.
Mind that the statistics used by the optimizer aren't sync'd with data changes - you might need to run UPDATE STATISTICS occaissionally too.