I have sort of a general question(s) that I'm hoping someone can help with....
I am using SSMS 2008 and I have an SP that supports a web page. When an end user selects the "All" selection on the page, the page times out. I updated the stored proc to include a "WITH RECOMPILE" option and now the page does not time out and loads appropriately.
However (there's always a however!), the performance on the site has taken a hit. While the page does load, it takes longer than it should and it's affecting other selections on the page.
I understand why it's now taking longer, but I don't understand why I had to add the recompile code in order for the page to load correctly. My questions are as follows:
1. If I keep the recompile code, is there anyway to speed up performance?
2. If I eliminate the recompile code, are there any work arounds I can implement to fix the time out of the page?
Can anyone offer any direction and/or documentation?