I have a program that uses a Stored Procedure to create a report. Unfortunatly the SP has a problem in the VB6 code. The program could pull the information perfectly until 2 days ago. Now the program times out after 60 seconds after sending the params to the sp. I can run the sp in Management Studio and it takes approx. 3 seconds to pull 88 rows. But if I was to try to run the report in the program it takes close to 9 minutes. After making a copy of the db and attaching to another machine I had the same problem but found the solution. Parameter Sniffers. I updated to SP on the second machine and I can run the report with no problem. But after I update on the Live machine the SP takes well over 3 minutes to return the data.
Anybody ever run into this problem? I feel as though I am in an endless loop here. I can't setup the second machine to be the live machine because it is a Dev machine and things change often.
Any help is greatly appreciated.
Thanks