0

I have a stored procedure which occasionally times out when called from our website (through the website connection pool). Once it has timed out, it has always been locked into the time-out, until the procedure is recompiled using drop/create or sp_recompile from a Management Studio session.

While it is timing out, there is no time-out using the same parameters for the same procedure using Management Studio.

Doing an "ALTER PROCEDURE" through Management Studio and (fairly drastically) changing the internal execution of the procedure did NOT clear the time out - it wouldn't clear until a full sp_recompile was run.

The stored procedure ends with OPTION (RECOMPILE)

The procedure calls two functions, which are used ubiquitously throughout the rest of the product. The other procedures which use these functions (in similar ways) all work, even during a period where the procedure in question is timing out.

If anyone can offer any further advice as to what could be causing this time out it would be greatly appreciated.

The stored procedure is as below:

ALTER PROCEDURE [dbo].[sp_g_VentureDealsCountSizeByYear] (
  @DateFrom         AS DATETIME = NULL
  ,@DateTo          AS DATETIME = NULL
  ,@ProductRegion   AS INT = NULL
  ,@PortFirmID      AS INT = NULL
  ,@InvFirmID       AS INT = NULL
  ,@SpecFndID       AS INT = NULL
) AS BEGIN
-- Returns the stats used for Market Overview
DECLARE @IDs    AS IDLIST
INSERT INTO @IDs
    SELECT IDs
    FROM dbo.fn_VentureDealIDs(@DateFrom,@DateTo,@ProductRegion,@PortFirmID,@InvFirmID,@SpecFndID)

CREATE TABLE #DealSizes (VentureID INT, DealYear INT, DealQuarter INT, DealSize_USD DECIMAL(18,2))
INSERT INTO #DealSizes
    SELECT vDSQ.VentureID, vDSQ.DealYear, vDSQ.DealQuarter, vDSQ.DealSize_USD
    FROM dbo.fn_VentureDealsSizeAndQuarter(@IDs) vDSQ

SELECT 
    yrs.Years Heading
    ,COUNT(vDSQ.VentureID)          AS Num_Deals
    ,SUM(vDSQ.DealSize_USD) AS DealSize_USD  
FROM tblYears yrs
    LEFT OUTER JOIN #DealSizes vDSQ ON vDSQ.DealYear = yrs.Years
WHERE (
        ((@DateFrom IS NULL) AND (yrs.Years >= (SELECT MIN(DealYear) FROM #DealSizes))) -- If no minimum year has been passed through, take all years from the first year found to the present.
            OR
        ((@DateFrom IS NOT NULL) AND (yrs.Years >= DATEPART(YEAR,@DateFrom)))   -- If a minimum year has been passed through, take all years from that specified to the present.
    ) AND (
        ((@DateTo IS NULL) AND (yrs.Years <= (SELECT MAX(DealYear) FROM #DealSizes))) -- If no maximum year has been passed through, take all years up to the last year found.
            OR 
        ((@DateTo IS NOT NULL) AND (yrs.Years <= DATEPART(YEAR,@DateTo)))   -- If a maximum year has been passed through, take all years up to that year.
    )
GROUP BY yrs.Years
ORDER BY Heading DESC
OPTION (RECOMPILE)
END
mrmillsy
  • 495
  • 3
  • 14

1 Answers1

2

If you wanted to recompile SP each time it is executed, you should have declared it with recompile; your syntax recompiles last select only:

ALTER PROCEDURE [dbo].[sp_g_VentureDealsCountSizeByYear] (
  @DateFrom         AS DATETIME = NULL
  ,@DateTo          AS DATETIME = NULL
  ,@ProductRegion   AS INT = NULL
  ,@PortFirmID      AS INT = NULL
  ,@InvFirmID       AS INT = NULL
  ,@SpecFndID       AS INT = NULL
) WITH RECOMPILE

I could not tell which part of your procedure causes problems. You might try commenting out select part to see if creating temp tables from table functions produces performance issue; if it does not, then the query itself is a problem. You might rewrite filter as following:

WHERE (@DateFrom IS NULL OR yrs.Years >= DATEPART(YEAR,@DateFrom))
  AND (@DateTo   IS NULL OR yrs.Years <= DATEPART(YEAR,@DateTo))

Or, perhaps better, declare startYear and endYear variables, set them accordingly and change where like this:

declare @startYear int
set @startYear = isnull (year(@DateFrom), (SELECT MIN(DealYear) FROM #DealSizes))
declare @endYear int
set @endYear = isnull (year(@DateTo), (SELECT MAX(DealYear) FROM #DealSizes))
...
where yrs.Year between @startYear and @endYear

If WITH RECOMPILE does not solve the problem, and removing last query does not help either, then you need to check table functions you use to gather data.

Nikola Markovinović
  • 18,963
  • 5
  • 46
  • 51
  • That's great, thanks Nikola. I'll give them a go sequentially. The difficulty of course is that I don't know how to reproduce the error, so testing is extremely laborious - I can never be sure if it has been solved unless it times out again (in which case I know it hasn't been). Ideally, I would prefer not to have to recompile each time, but if it solves the issue then to be honest that is good enough (for now) as this is a relatively low priority development concern. – mrmillsy Jul 19 '12 at 09:59
  • 1
    @mrmillsy After re-reading everything, I think I would start with dbo.fn_VentureDealIDs. It might be suffering from [parameter sniffing](http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx). – Nikola Markovinović Jul 19 '12 at 10:18
  • Yeah, I did look there at first - in fact I have a question open from a while back relating to this: at http://stackoverflow.com/questions/10799725/tsql-ivf-causing-timeout-in-asp-net-application. That function is the absolute bones of this application however and nowhere else on the application is suffering even slightly. – mrmillsy Jul 19 '12 at 10:57
  • By the way, I have added the correct RECOMPILE method to the store procedure for now. I will test it periodically, and if it is still not timing out after a week I will come back and mark this as the answer. – mrmillsy Jul 19 '12 at 11:01
  • I'm satisfied the procedure is working now. Creating the stored procedure WITH RECOMPILE was sufficient, thanks. – mrmillsy Jul 23 '12 at 10:46