Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a complex query which runs in 2 seconds in the query window, but about 5 minutes as a Stored Procedure. Why is it taking so much longer to run as a stored procedure?

Here's what my query looks like.

It takes a specific set of records (identified by @id and @createdDate), and a specific time frame (1 year starting from @startDate) and returns a summarized list of letters sent and estimated payments received as a result of those letters.

CREATE PROCEDURE MyStoredProcedure
    @id int,
    @createdDate varchar(20),
    @startDate varchar(20)

 AS
SET NOCOUNT ON

    -- Get the number of records * .7
    -- Only want to return records containing letters that were sent on 70% or more of the records
    DECLARE @limit int
    SET @limit = IsNull((SELECT Count(*) FROM RecordsTable WITH (NOLOCK) WHERE ForeignKeyId = @id AND Created = @createdDate), 0) * .07

    SELECT DateSent as [Date] 
        , LetterCode as [Letter Code]
        , Count(*) as [Letters Sent]
        , SUM(CASE WHEN IsNull(P.DatePaid, '1/1/1753') BETWEEN DateSent AND DateAdd(day, 30, DateSent) THEN IsNull(P.TotalPaid, 0) ELSE 0 END) as [Amount Paid]
    INTO #tmpTable
    FROM (

        -- Letters Table. Filter for specific letters
        SELECT DateAdd(day, datediff(day, 0, LR.DateProcessed), 0) as [DateSent] -- Drop time from datetime
            , LR.LetterCode -- Letter Id
            , M.RecordId -- Record Id
        FROM LetterRequest as LR WITH (NOLOCK)
        INNER JOIN RecordsTable as M WITH (NOLOCK) ON LR.RecordId = M.RecordId
        WHERE ForeignKeyId = @id AND Received = @createdDate
            AND LR.Deleted = 0 AND IsNull(LR.ErrorDescription, '') = ''
            AND LR.DateProcessed BETWEEN @startDate AND DateAdd(year, 1, @startDate)
            AND LR.LetterCode IN ('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o')
    ) as T
    LEFT OUTER JOIN (

        -- Payment Table. Payments that bounce are entered as a negative payment and are accounted for
        SELECT PH.RecordId, PH.DatePaid, PH.TotalPaid
        FROM PaymentHistory as PH WITH (NOLOCK)
            INNER JOIN RecordsTable as M WITH (NOLOCK) ON PH.RecordId = M.RecordId
            LEFT OUTER JOIN PaymentHistory as PR WITH (NOLOCK) ON PR.ReverseOfUId = PH.UID
        WHERE PH.SomeString LIKE 'P_' 
            AND PR.UID is NULL 
            AND PH.DatePaid BETWEEN @startDate AND DateAdd(day, 30, DateAdd(year, 1, @startDate))
            AND M.ForeignKeyId = @id AND M.Created = @createdDate
    ) as P ON T.RecordId = P.RecordId

    GROUP BY DateSent, LetterCode
    --HAVING Count(*) > @limit
    ORDER BY DateSent, LetterCode

    SELECT *
    FROM #tmpTable
    WHERE [Letters Sent] > @limit

    DROP TABLE #tmpTable

The end result looks like this:

Date        Letter Code     Letters Sent    Amount Paid
1/1/2012    a               1245            12345.67
1/1/2012    b               2301            1234.56
1/1/2012    c               1312            7894.45
1/1/2012    a               1455            2345.65
1/1/2012    c               3611            3213.21

I'm having problems figuring out where the slowdown is, because everything runs extremely fast in the query editor. It's only when I move the query to a stored procedure that it starts taking so long to run.

I'm sure it has something to do with the query execution plan getting generated, but I don't know enough about SQL to identify what could be causing the problem.

It should probably be noted that all the tables used in the query have millions of records.

Can someone explain to me why this is taking so much longer to run as a stored procedure than in the query editor, and help me identify what part of my query could be causing performance issues when run as a stored procedure?

share|improve this question
8  
Probably parameter sniffing meaning you are using a cached inappropriate plan. See Slow in the Application, Fast in SSMS? Understanding Performance Mysteries –  Martin Smith Mar 19 '12 at 15:35
    
@MartinSmith You're right, I now remember finding this out this years ago. The solution was to create a copy of the parameter, and use the copy in the queries instead of the actual parameter. If you post this as an answer I'll accept it :) –  Rachel Mar 19 '12 at 15:43
    
You could also look at using OPTIMIZE FOR or RECOMPILE hints. –  Martin Smith Mar 19 '12 at 15:47
    
possible duplicate of Suddenly Slow Execution Plan for Stored Proc –  Martin Smith Mar 19 '12 at 15:51
    
@MartinSmith Thanks. I'd prefer to avoid the RECOMPILE hint since I don't really want to recompile the query every time it's run, and the article you linked mentioned that copying parameters to a local variable is the equivalent as using OPTIMIZE FOR UNKNOWN, which seems to only be available in 2008 and later. I think for now I'll stick with copying parameters to a local variable, which brings my query execution time down back down to 1-2 seconds. –  Rachel Mar 19 '12 at 15:59

1 Answer 1

up vote 1 down vote accepted

As Martin pointed out in the comments, the problem is that the query is using a cached plan which is inappropriate for the parameters given.

The link he provided on Slow in the Application, Fast in SSMS? Understanding Performance Mysteries provided a lot of useful information which lead me to some solutions.

The solution I am currently using is to copy the parameters to local variables in the procedure, which I think makes SQL re-evaluate the execution plan for the query anytime it's run, so it picks the best execution plan for the parameters given instead of using an inappropriate cached plan for the query.

Other solutions which may work are using the OPTIMIZE FOR or RECOMPILE query hints.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.