Stored Procedure Timing Out Until Recompile

Hey all,

We have a problem with one of our MS SQL 2000 databases and some stored procedures.

I'm not sure exactly what the problem is, but these are the symptons....

The stored procedure runs without problems for a period of time. Abruptly, without warning it begins to time out when called from our web application.

Calling it through the query analyzer it runs within a second.

Forcing the stored procedure to recompile allows the web application to start calling it again without it timing out.

We have a DTS package that runs over night and imports a number of records (not sure on the exact numbers, but definately enough to make a difference to indexes) so this could be part of the problem although when I force a recompile I do not do any update stats or anything else.

I wrote a test script to call the stored procedure when it was timing out to ensure it wasn't a web application problem and the procedure continued to time out until the forced recompile. So I don't think the problem is there.

The stored procedure returns multiple results sets and when it starts timing out it is while it is returning the second results sets.

The code for the second results set is...


Select avg(round(p.PricingValue, 5)) as Average, stdev(round(p.PricingValue, 5)) as StdDev, min(p.CaptureDate) as FromDate, max(p.CaptureDate) as ToDate
From Pricing p
Inner Join Security s
On p.SecurityID = s.SecurityID
Left Outer Join Issuer i
On s.IssuerID = i.IssuerID
WHERE p.PricingTypeID = @PricingType
And p.TenorTypeID = @TenorType
And p.CaptureDate Between @DateFrom And @DateTo
AND p.SecurityID IN ( SELECT SecurityId FROM UserResult ur WHERE ur.UserResultSelected = 1 AND ur.UserID = @userID )


Does anyone have any idea what might be going on here?

Regards
Shaun