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

If it's not confidential, you can show us the SP to see if we can optimize it. I think in connection string you can specify the time out value play with it, it may solve the problem on the first machine.

I had changed the sp a bit and got it to work in the program. It now takes about 10 seconds program side to run the report.
This is the original problem.

ALTER   PROCEDURE [dbo].[MAR] 
	@PatientID int = 0, 
	@FacilityID int = 0, 
	@IncludeVoid bit = 0, 
	@IncludeDisc bit = 1

AS
SET NOCOUNT ON
--Remainder of SP

The SP in Management Studio ran great but using VB6 took forever. I updated the SP to the following.

ALTER   PROCEDURE [dbo].[MAR] 
	@PatientID int, 
	@FacilityID int, 
	@IncludeVoid bit, 
	@IncludeDisc bit 

AS
SET NOCOUNT ON
DECLARE 
	@PatientID1 int, 
	@FacilityID1 int, 
	@IncludeVoid1 int, 
	@IncludeDisc1 int 
SET          @PatientID1 = @PatientID
SET	@FacilityID1 = @FacilityID
SET	@IncludeVoid1 = @IncludeVoid
SET	@IncludeDisc1 = @IncludeDisc
--Remainder SP
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.