• harshada_joshi - Monday, November 27, 2017 8:39 PM

    I am using SQL server R2.
    I have stored procedure which takes too much time to execute.
    I have posted estimated plane here,
    https://www.brentozar.com/pastetheplan/?id=rk6Z6MKxf
    Can anyone help me to find out why my stored procedure is slow.
    --------------
    We have AWS server for testing. Same stored procedure doesn't take much time on our testing server (same Sql server 2008 R2) or our local server (SQL server 2012).
    But our clients server it runs too slow.

    You have a date range filter which uses crazy "alternative" logic. Change it to something more standard and it's likely to be SARGable (Google it). Does this - '4/27/17' - get passed in as a variable? You want to end up with something like this:
    WHERE (@CompareDate IS NULL OR Schedule.StartDate >= @CompareDate)
     AND (@CompareDate IS NULL OR Schedule.EndDate < DATEADD(DAY,1,@CompareDate))

    You have another crazy filter in your WHERE clause which is based on the variable @ChecklistTypeID. I think it can be changed to this:
    WHERE (@ChecklistTypeID IS NULL AND Checklist.AudittypeID = 20)
     OR (Checklist.ChecklistTypeID = @ChecklistTypeID)

    Whether or not you're planning to show your code to the world, it's always good to have it nicely formatted and commented for readability.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden