RVO (11/4/2013)
Guys, I am so thankful for your help.I think parameter sniffing is definitely might be the root cause.
I am rewriting procedure now to use local variables.
Will let you know on the outcome results soon.
Please be aware that symptoms might look like parameter sniffing but most likely it isn't. Parameter sniffing would be my last option to look at.
Your sproc takes 7 secs to load for every consecutive run since the first one because most likely data and plan is cached. Have you looked at indices and stats before changing the code?