Parameter Sniffing
-
This is not a question! I got caught by the parameter sniffing[^] issue the other day, it was a classic, I had built a proc that takes in 2 parameters, applies then against a bunch of tables and inserts the results into a table variable to return a summary of the current periods processing status. It worked perfectly during development, ran like a dream (<10sec) during the first round of UAT. Then it spat the dummy and I was getting a timeout from the service, the procedure ran perfectly from SSMS but from the client it just refused to returned a result. 3 hours later having exhausted all probabilities the little bulb clicked, USE LOCAL VARIABLES. I was applying the parameters multiple times to different tables, by changing to a pair of local variables this fixed the problem, classic case of parameter sniffing.
Never underestimate the power of human stupidity RAH