Slow running Stored Procedure when using Date Parameters

  • I have a SP as follows:

    CREATE PROCEDURE dbo.MyProcedure @StartDate Datetime, @EndDate Datetime, @ReportingPeriod varchar(254)
    As
    DECLARE @ReportStartDate Datetime
    DECLARE @ReportEndDate Datetime
    SET @ReportStartDate = dbo.fn_Get_Date_Params(@ReportingPeriod, @StartDate, @EndDate, 1)
    SET @ReportEndDate = dbo.fn_Get_Date_Params(@ReportingPeriod, @StartDate, @EndDate, 2)
    SELECT
           *
    FROM
           dbo.MyTable MT
    WHERE
           MT.ContactDate >= @ReportStartDate
           AND MT.ContactDate < @ReportEndDate

    This SP runs slow (around 6 minutes) when using the date parameters as returned by the User Defined Function, even though the UDF executes in less than a second.  If I swap those out for the dates that are incoming parameters to the SP (@StartDate and @EndDate), it runs in about 20 seconds.  The approaches seem similar, but the execution time is drastically different, and I’m not able to determine why that is.
    Any help is greatly appreciated!
    Thanks,
    Brent

  • dukesbrent - Monday, February 25, 2019 8:56 AM

    I have a SP as follows:

    CREATE PROCEDURE dbo.MyProcedure @StartDate Datetime, @EndDate Datetime, @ReportingPeriod varchar(254)
    As
    DECLARE @ReportStartDate Datetime
    DECLARE @ReportEndDate Datetime
    SET @ReportStartDate = dbo.fn_Get_Date_Params(@ReportingPeriod, @StartDate, @EndDate, 1)
    SET @ReportEndDate = dbo.fn_Get_Date_Params(@ReportingPeriod, @StartDate, @EndDate, 2)
    SELECT
           *
    FROM
           dbo.MyTable MT
    WHERE
           MT.ContactDate >= @ReportStartDate
           AND MT.ContactDate < @ReportEndDate

    This SP runs slow (around 6 minutes) when using the date parameters as returned by the User Defined Function, even though the UDF executes in less than a second.  If I swap those out for the dates that are incoming parameters to the SP (@StartDate and @EndDate), it runs in about 20 seconds.  The approaches seem similar, but the execution time is drastically different, and I’m not able to determine why that is.
    Any help is greatly appreciated!
    Thanks,
    Brent

    First of all, could you share the code for your UDF? That could be the problem.
    Also, try to create an additional procedure where you send the Report Dates instead of the regular Dates to get a better execution plan.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • What indexes have you got on dbo.MyTable? I think different index usage between the queries is the problem as you are stopping parameter sniffing by setting the dates within the stored procedure. 
    Have you looked at the execution plan for the different runs?
    Have you tried this:
    SELECT
       *
       FROM dbo.MyTable MT
      WHERE MT.ContactDate >= dbo.fn_Get_Date_Params(@ReportingPeriod, @StartDate, @EndDate, 1)
        AND MT.ContactDate < dbo.fn_Get_Date_Params(@ReportingPeriod, @StartDate, @EndDate, 2)

  • dukesbrent - Monday, February 25, 2019 8:56 AM

    Your approach to trying to write procedural code in SQL is to mimic Fortran! First of all, the name of the procedure should take the format <verb>_<object> and not that silly "my_procedure" because the procedure does something, which means a verb on the object of the action. You talk about having dates, but you don't use the date data type. The original Sybase language had to use the @ prefix because T SQL uses a simple one pass compiler. The ANSI/ISO standard PSM has a keyword on its parameters to indicate their input, output, or both.

    In fact did you know that the use of the "FN_" prefix goes back to the first versions of the Fortran programming language? It was also carried over into BASIC. It indicates a one line function. But since SQL is supposed to be a declarative language. We really don't like functions and we sure as hell hate local variables. Please get rid of this Fortran code and start writing SQL.

    CREATE PROCEDURE <Verb>_<Object>
      (@in_report_start_date DATE,
      @in_report_end_date DATE,
      @in_reporting_period VARCHAR(254))
    AS
    SELECT * --- never use * in a select list in production code!
    FROM <real table name> AS MT
    WHERE MT.contact_date BETWEEN @in_report_start_date
         AND @in_report_end_date;
     AND <magic stuff with @in_reporting_period>;

    >> This SP runs slow (around 6 minutes) when using the date parameters as returned by the User Defined Function, even though the UDF executes in less than a second.<<

    We never intended for anybody to use UDF. This is something Microsoft added and there's no way in hell it can be optimized. No, not even in theory. I don't know what your reporting period is since you didn't bother to post any DDL for your UDF.

    I realize that you have to post a skeleton in a forum, but we really need to have enough information to answer a question. However, please trust me after having written the standards for this language and doing it for over 30 years, I'm pretty sure the real problem for the rest of your career will be that you're still writing Fortran and never really learn to think in SQL. Sorry if that's brutal but it's very honest.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • dukesbrent - Monday, February 25, 2019 8:56 AM

    I have a SP as follows:

    CREATE PROCEDURE dbo.MyProcedure @StartDate Datetime, @EndDate Datetime, @ReportingPeriod varchar(254)
    As
    DECLARE @ReportStartDate Datetime
    DECLARE @ReportEndDate Datetime
    SET @ReportStartDate = dbo.fn_Get_Date_Params(@ReportingPeriod, @StartDate, @EndDate, 1)
    SET @ReportEndDate = dbo.fn_Get_Date_Params(@ReportingPeriod, @StartDate, @EndDate, 2)
    SELECT
           *
    FROM
           dbo.MyTable MT
    WHERE
           MT.ContactDate >= @ReportStartDate
           AND MT.ContactDate < @ReportEndDate

    This SP runs slow (around 6 minutes) when using the date parameters as returned by the User Defined Function, even though the UDF executes in less than a second.  If I swap those out for the dates that are incoming parameters to the SP (@StartDate and @EndDate), it runs in about 20 seconds.  The approaches seem similar, but the execution time is drastically different, and I’m not able to determine why that is.
    Any help is greatly appreciated!
    Thanks,
    Brent

    The problem is likely since you are using variables in the query, the stored procedure will generate an execution plan in "optimize for unknown" mode.  If you use the parameters directly in your query, the first time the procedure is run the generated execution plan will do what is called "parameter sniffing" to determine what execution plan to use.  Parameter sniffing isn't always a bad thing, it's only bad when the first sniffed parameter represents a bad or worst case scenario as opposed to the typical scenario.
    https://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/

  • Here is the code for the UDF:

    CREATE FUNCTION [dbo].[fn_Get_Date_Params]
    (
        @strPeriod varchar(254),
        @dtmStart_Date datetime,
        @dtmEnd_Date datetime,
        @dtmGetDate datetime,
        @intWhichOne int
    )

    RETURNS datetime

    AS

    */

    BEGIN

    DECLARE @dtmPeriodBegin datetime
    DECLARE @dtmPeriodEnd datetime
    DECLARE @intCurMonth int
    DECLARE @intCurYear int
    DECLARE @intCurDay int
    DECLARE @dtmDate datetime
    --------------------------------
    SET @intCurYear = right(Convert(varchar(10), @dtmGetDate,101),4)
    SET @intCurMonth = left(Convert(varchar(10), @dtmGetDate,101),2)
    SET @intCurDay = DATEPART(dw, @dtmGetDate)

    ------------------------------
    IF @strPeriod = 'Yesterday'
      BEGIN
      
       SET @dtmPeriodBegin = CONVERT(VARCHAR(10), ( @dtmGetDate - 1 ), 101)
       SET @dtmPeriodEnd = CONVERT(VARCHAR(10), ( @dtmGetDate ), 101)
      END
    ------------------------------
    IF @strPeriod = 'Last Full Weekday'
      BEGIN
       IF @intCurDay = 1
        BEGIN
          SET @dtmPeriodBegin = CONVERT(VARCHAR(10), ( @dtmGetDate - 2 ), 101)
          SET @dtmPeriodEnd = CONVERT(VARCHAR(10), ( @dtmGetDate ) - 1, 101)
        END
       IF @intCurDay = 2
        BEGIN
          SET @dtmPeriodBegin = CONVERT(VARCHAR(10), ( @dtmGetDate - 3 ), 101)
          SET @dtmPeriodEnd = CONVERT(VARCHAR(10), ( @dtmGetDate - 2 ), 101)
        END
       IF @intCurDay = 3
        BEGIN
          SET @dtmPeriodBegin = CONVERT(VARCHAR(10), ( @dtmGetDate - 1 ), 101)
          SET @dtmPeriodEnd = CONVERT(VARCHAR(10), ( @dtmGetDate ), 101)
        END
       IF @intCurDay = 4
        BEGIN
          SET @dtmPeriodBegin = CONVERT(VARCHAR(10), ( @dtmGetDate - 1 ), 101)
          SET @dtmPeriodEnd = CONVERT(VARCHAR(10), ( @dtmGetDate ), 101)
        END
       IF @intCurDay = 5
        BEGIN
          SET @dtmPeriodBegin = CONVERT(VARCHAR(10), ( @dtmGetDate - 1 ), 101)
          SET @dtmPeriodEnd = CONVERT(VARCHAR(10), ( @dtmGetDate ), 101)
        END
       IF @intCurDay = 6
        BEGIN
          SET @dtmPeriodBegin = CONVERT(VARCHAR(10), ( @dtmGetDate - 1 ), 101)
          SET @dtmPeriodEnd = CONVERT(VARCHAR(10), ( @dtmGetDate ), 101)
        END
       IF @intCurDay = 7
        BEGIN
          SET @dtmPeriodBegin = CONVERT(VARCHAR(10), ( @dtmGetDate - 1 ), 101)
          SET @dtmPeriodEnd = CONVERT(VARCHAR(10), ( @dtmGetDate ), 101)
        END

      END
    ------------------------------
    IF @strPeriod = 'Last Full Week'
        BEGIN
            IF @intCurDay = 1
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 7), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate), 101)
                END
            IF @intCurDay = 2
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 8), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 1), 101)
                END
            IF @intCurDay = 3
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 9), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 2), 101)
                END
            IF @intCurDay = 4
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 10), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 3), 101)
                END
            IF @intCurDay = 5
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 11), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 4), 101)
                END
            IF @intCurDay = 6
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 12), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 5), 101)
                END
            IF @intCurDay = 7
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 13), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 6), 101)
                END    
        END
    -------------------------------
    IF @strPeriod = 'Last Full Month'
        BEGIN
            IF @intCurMonth = 1
                BEGIN
                    SET @dtmPeriodBegin = str(12) + '/1/' + str(@intCurYear-1)
                    SET @dtmPeriodEnd = str(@intCurMonth) + '/1/' + str(@intCurYear)
                END
            ELSE
                BEGIN
                    SET @dtmPeriodBegin = str(@intCurMonth-1) + '/1/' + str(@intCurYear)
                    SET @dtmPeriodEnd = str(@intCurMonth) + '/1/' + str(@intCurYear)    
                END
        END
    ---------------------------------
    IF @strPeriod = 'Last Full Quarter'
        BEGIN
            IF @intCurMonth in (1)
                BEGIN
                    SET @dtmPeriodBegin = str(10) + '/1/' + str(@intCurYear-1)
                    SET @dtmPeriodEnd = str(@intCurMonth) + '/1/' + str(@intCurYear)
                END
            IF @intCurMonth in (2)
                BEGIN
                    SET @dtmPeriodBegin = str(10) + '/1/' + str(@intCurYear-1)
                    SET @dtmPeriodEnd = str(@intCurMonth-1) + '/1/' + str(@intCurYear)
                END
            IF @intCurMonth in (3)
                BEGIN
                    SET @dtmPeriodBegin = str(10) + '/1/' + str(@intCurYear-1)
                    SET @dtmPeriodEnd = str(@intCurMonth-2) + '/1/' + str(@intCurYear)
                END
            IF @intCurMonth in (4,7,10)
                BEGIN
                    SET @dtmPeriodBegin = str(@intCurMonth-3) + '/1/' + str(@intCurYear)
                    SET @dtmPeriodEnd = str(@intCurMonth) + '/1/' + str(@intCurYear)    
                END
            IF @intCurMonth in (5,8,11)
                BEGIN
                    SET @dtmPeriodBegin = str(@intCurMonth-4) + '/1/' + str(@intCurYear)
                    SET @dtmPeriodEnd = str(@intCurMonth-1) + '/1/' + str(@intCurYear)    
                END
            IF @intCurMonth in (6,9,12)
                BEGIN
                    SET @dtmPeriodBegin = str(@intCurMonth-5) + '/1/' + str(@intCurYear)
                    SET @dtmPeriodEnd = str(@intCurMonth-2) + '/1/' + str(@intCurYear)    
                END
        END
    ---------------------------------
    if @strPeriod = 'Last 7 Days'
        BEGIN
            SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 7), 101)
            SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate), 101)
        END

    -------------------------------------------
    IF @strPeriod = 'Last Full 18 Months'
                
    --10/23/06 Per Tevor, End Date always needs to include the Friday before the report is run to get all the previous weeks' postings.
        BEGIN
            IF @intCurDay = 1 --Sunday
                        BEGIN
                            SET @dtmPeriodEnd = CONVERT( varchar(10), (@dtmGetDate - 1), 101)--@dtmGetDate - 8
                            SET @dtmPeriodBegin = dateadd (mm, -18, @dtmperiodend)
                        END
                IF @intCurDay = 2 --Monday
                        BEGIN
                            SET @dtmPeriodEnd = CONVERT( varchar(10), (@dtmGetDate - 2), 101)--@dtmGetDate - 2
                            SET @dtmPeriodBegin = dateadd (mm, -18, @dtmperiodend)
                        END
                IF @intCurDay = 3 --Tuesday
                        BEGIN
                            SET @dtmPeriodEnd = CONVERT( varchar(10), (@dtmGetDate - 3), 101)--@dtmGetDate- 3
                            SET @dtmPeriodBegin = dateadd (mm, -18, @dtmperiodend)
                        END
                IF @intCurDay = 4 --Wednesday
                        BEGIN
                            SET @dtmPeriodEnd = CONVERT( varchar(10), (@dtmGetDate - 4), 101)--@dtmGetDate - 4
                            SET @dtmPeriodBegin = dateadd(mm, -18, @dtmperiodend)
                        END
                IF @intCurDay = 5 --Thursday
                        BEGIN
                            SET @dtmPeriodEnd = CONVERT( varchar(10), (@dtmGetDate - 5), 101) --@dtmGetDate - 5
                            SET @dtmPeriodBegin = dateadd (mm, -18, @dtmperiodend)
                        END
                IF @intCurDay =6 --Friday
                        BEGIN
                            SET @dtmPeriodEnd = CONVERT( varchar(10), (@dtmGetDate - 6), 101)--@dtmGetDate - 6
                            SET @dtmPeriodBegin = dateadd (mm, -18, @dtmperiodend)
                        END
                IF @intCurDay = 7 --Saturday
                        BEGIN
                            SET @dtmPeriodEnd = CONVERT( varchar(10), (@dtmGetDate - 7), 101)--@dtmGetDate - 7
                            SET @dtmPeriodBegin = dateadd (mm, -18, @dtmperiodend)
                        END
        END
    ---------------------------------
    --Calculates one year from the prior month of the run date----
    ---------------------------------
    IF @strPeriod = 'Last Full Year'
        BEGIN
            IF @intCurMonth = 1
                BEGIN
                    SET @dtmPeriodEnd = str(1) + '/1/' + str(@intCurYear)
                    SET @dtmPeriodBegin = str(@intCurMonth) + '/1/' + str(@intCurYear-1)
                END
            ELSE
                BEGIN
                    SET @dtmPeriodBegin = str(@intCurMonth ) + '/1/' + str(@intCurYear-1)
                    SET @dtmPeriodEnd = str(@intCurMonth) + '/1/' + str(@intCurYear)    
                END
        END
    ---------------------------------
    IF @strPeriod = 'Last Full Calendar Year'
        BEGIN    
            SET @dtmPeriodBegin = str(1) + '/1/' + str(@intCurYear-1)
            SET @dtmPeriodEnd = str(1) + '/1/' + str(@intCurYear)    
        END
    ---------------------------------
    IF @strPeriod = 'Last Full Fiscal Year'
        BEGIN
            IF @intCurMonth > 6
                BEGIN
                    SET @dtmPeriodBegin = str(7) + '/1/' + str(@intCurYear-1)
                    SET @dtmPeriodEnd = str(7) + '/1/' + str(@intCurYear)
                END
            ELSE
                BEGIN
                    SET @dtmPeriodBegin = str(7) + '/1/' + str(@intCurYear-2)
                    SET @dtmPeriodEnd = str(7) + '/1/' + str(@intCurYear-1)
                END
        END    
    ---------------------------------
    IF @strPeriod = 'Current Calendar Year To Date'    
        BEGIN
        
                    SET @dtmPeriodBegin = str(1) + '/1/' + str(@intCurYear)
                    SET @dtmPeriodEnd = CONVERT( varchar(10), (@dtmGetDate + 1), 101)
        END
    ---------------------------------
    IF @strPeriod = 'Current Month To Date'    
        BEGIN
        
                    SET @dtmPeriodBegin = str(@intCurMonth) + '/1/' + str(@intCurYear)
                    SET @dtmPeriodEnd = CONVERT( varchar(10), (@dtmGetDate + 1), 101)
        END
    ---------------------------------------------
    IF @strPeriod = 'Current Fiscal Year to Date'
        BEGIN
            IF @intCurMonth > 6
                BEGIN
                    SET @dtmPeriodBegin = str(7) + '/1/' + str(@intCurYear)
                    SET @dtmPeriodEnd = CONVERT( varchar(10), (@dtmGetDate + 1), 101)
                END
            ELSE
                BEGIN
                    SET @dtmPeriodBegin = str(7) + '/1/' + str(@intCurYear-1)
                    SET @dtmPeriodEnd = CONVERT( varchar(10), (@dtmGetDate + 1), 101)
                    
                END
        END
    ---------------------------------
    IF @strPeriod = 'Curr Fisc Yr Thru Last Full Mo'        
        BEGIN
            IF @intCurMonth > 6
                BEGIN
                    SET @dtmPeriodBegin = str(7) + '/1/' + str(@intCurYear)
                    SET @dtmPeriodEnd = str(@intCurMonth) + '/1/' + str(@intCurYear)    
                END
            ELSE
                BEGIN
                    SET @dtmPeriodBegin = str(7) + '/1/' + str(@intCurYear-1)
                    SET @dtmPeriodEnd = str(@intCurMonth) + '/1/' + str(@intCurYear)    
                END
        END
    -- ---------------------------------
    If @strPeriod = 'Use Custom Period'
        BEGIN
            SET @dtmPeriodBegin = LEFT (CONVERT(VARCHAR(10),@dtmStart_Date,101),10)
            SET @dtmPeriodEnd = LEFT(CONVERT(VARCHAR(10), @dtmEnd_Date + 1,101),10)
        END
    ---------------------------------

    if @strPeriod = 'Next 7 Days'
        BEGIN
            SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate + 1), 101)
            SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate + 7), 101)
        END
    ------------------------------
    IF @strPeriod = 'Next Full Month'
        BEGIN
            IF @intCurMonth = 12
                BEGIN
                    SET @dtmPeriodBegin = str(1) + '/1/' + str(@intCurYear+1)
                    SET @dtmPeriodEnd = str(2) + '/1/' + str(@intCurYear+1)
                END
            ELSE IF @intCurMonth = 11
                BEGIN
                    SET @dtmPeriodBegin = str(@intCurMonth+1) + '/1/' + str(@intCurYear)
                    SET @dtmPeriodEnd = str(1) + '/1/' + str(@intCurYear+1)
                END
            ELSE
                BEGIN
                    SET @dtmPeriodBegin = str(@intCurMonth + 1) + '/1/' + str(@intCurYear)
                    SET @dtmPeriodEnd = str(@intCurMonth+2) + '/1/' + str(@intCurYear)    
                END
        END

    --------------------------------------------------------------------------
    /*
    Used to identify the start and end date of the two prio completed weeks, where a week is defined as Sunday
    through Saturday.
    */
    IF @strPeriod = 'Last Full 2 Weeks'
        BEGIN
            IF @intCurDay = 1 --Sunday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 14), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate), 101)
                END
            IF @intCurDay = 2 --Monday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 15), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 1), 101)
                END
            IF @intCurDay = 3 --Tuesday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 16), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 2), 101)
                END
            IF @intCurDay = 4 --Wedensday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 17), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 3), 101)
                END
            IF @intCurDay = 5 --Thursday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 18), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 4), 101)
                END
            IF @intCurDay = 6 --Friday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 19), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 5), 101)
                END
            IF @intCurDay = 7 --Saturday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 20), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 6), 101)
                END    
        END
    --------------------------------------------------------------------------
    /*
    Used to identify the date of the last Frinday.
    */
    IF @strPeriod = 'Last Friday'
        BEGIN
            IF @intCurDay = 1 --Sunday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 2), 101)
                END
            IF @intCurDay = 2 --Monday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 3), 101)
                END
            IF @intCurDay = 3 --Tuesday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 4), 101)
                END
            IF @intCurDay = 4 --Wedensday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 5), 101)
                END
            IF @intCurDay = 5 --Thursday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 6), 101)
                END
            IF @intCurDay = 6 --Friday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 7), 101)
                END
            IF @intCurDay = 7 --Saturday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 1), 101)
                END    
        END
    -------------------------------
    IF @strPeriod = 'Last Full 12 Months'
        BEGIN
            SET @dtmPeriodEnd = str(@intCurMonth) + '/1/' + str(@intCurYear)
            SET @dtmPeriodBegin = dateadd (mm, -12, @dtmperiodend)
        END
    ------------------------------
    IF @strPeriod = 'Last Full 6 Months'
        BEGIN
            SET @dtmPeriodEnd = str(@intCurMonth) + '/1/' + str(@intCurYear)
            SET @dtmPeriodBegin = dateadd (mm, -6, @dtmperiodend)
        END
    ------------------------------
    IF @strPeriod = 'Prior Last Full Week'
        BEGIN
            IF @intCurDay = 1 --Sunday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 14), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 7), 101)
                END
            IF @intCurDay = 2 --Monday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 15), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 8), 101)
                END
            IF @intCurDay = 3 --Tuesday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 16), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 9), 101)
                END
            IF @intCurDay = 4 --Wednesday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 17), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 10), 101)
                END
            IF @intCurDay = 5 --Thursday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 18), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 11), 101)
                END
            IF @intCurDay = 6 --Friday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 19), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 12), 101)
                END
            IF @intCurDay = 7 --Saturday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 20), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 13), 101)
                END    
        END
    ---------------------------------
    IF @strPeriod = 'Current Calendar Year To Last Full Month'    
        BEGIN
            SET @dtmPeriodBegin = str(1) + '/1/' + str(@intCurYear)
            SET @dtmPeriodEnd = str(@intCurMonth) + '/1/' + str(@intCurYear)
        END
    ------------------------------
    IF @strPeriod = 'Last 365 Days'
        BEGIN
            SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 365), 101)
            SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate), 101)
        END
    ------------------------------
    if @strPeriod = 'Prior 90 Days'
        BEGIN
            SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 90), 101)
            SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate + 1), 101)
        END

    ------------------------------
    IF @strPeriod = 'Yesterday From Business Day'
      BEGIN
       IF @intCurDay = 1
        BEGIN
          SET @dtmPeriodBegin = CONVERT(VARCHAR(10), ( @dtmGetDate - 2 ), 101)
          SET @dtmPeriodEnd = CONVERT(VARCHAR(10), ( @dtmGetDate ) - 1, 101)
        END
       IF @intCurDay = 2
        BEGIN
          SET @dtmPeriodBegin = CONVERT(VARCHAR(10), ( @dtmGetDate - 3 ), 101)
          SET @dtmPeriodEnd = CONVERT(VARCHAR(10), ( @dtmGetDate ), 101)
        END
       IF @intCurDay = 3
        BEGIN
          SET @dtmPeriodBegin = CONVERT(VARCHAR(10), ( @dtmGetDate - 1 ), 101)
          SET @dtmPeriodEnd = CONVERT(VARCHAR(10), ( @dtmGetDate ), 101)
        END
       IF @intCurDay = 4
        BEGIN
          SET @dtmPeriodBegin = CONVERT(VARCHAR(10), ( @dtmGetDate - 1 ), 101)
          SET @dtmPeriodEnd = CONVERT(VARCHAR(10), ( @dtmGetDate ), 101)
        END
       IF @intCurDay = 5
        BEGIN
          SET @dtmPeriodBegin = CONVERT(VARCHAR(10), ( @dtmGetDate - 1 ), 101)
          SET @dtmPeriodEnd = CONVERT(VARCHAR(10), ( @dtmGetDate ), 101)
        END
       IF @intCurDay = 6
        BEGIN
          SET @dtmPeriodBegin = CONVERT(VARCHAR(10), ( @dtmGetDate - 1 ), 101)
          SET @dtmPeriodEnd = CONVERT(VARCHAR(10), ( @dtmGetDate ), 101)
        END
       IF @intCurDay = 7
        BEGIN
          SET @dtmPeriodBegin = CONVERT(VARCHAR(10), ( @dtmGetDate - 1 ), 101)
          SET @dtmPeriodEnd = CONVERT(VARCHAR(10), ( @dtmGetDate ), 101)
        END
      END

        ------------------------------
    --Here is where the UDF determines which date date should be returned to the SP
    IF @intWhichOne = 1
        SET @dtmDate = @dtmPeriodBegin
    ELSE
        SET @dtmDate = @dtmPeriodEnd

    RETURN
        @dtmDate

    END

    GO

    The SP code I included in my original post was a lot dumb-ed down version of the actual code, sorry for not mentioning that before.  There are additional tables involved in the FROM clause, and the join between the main table (which I denoted as MyTable) and another is showing up in the Execution Plan as a potential issue, regardless of how the parameters are used in the WHERE clause.  We use a proprietary Vendor supplied database, so that was my reason for not sharing the actual SP code.  I'll do some additional reading on the parameter sniffing to educate myself there.  The approach we're using here (getting some parameters from a report to determine the reporting period to be used) is one I've used for many years without seeing these types of performance issues.  Granted I'm now using on the same vendor supplied database, although at a different organization, with potentially different SQL Server configurations, indexes, etc.

  • dukesbrent - Monday, February 25, 2019 2:47 PM

    Here is the code for the UDF:

    CREATE FUNCTION [dbo].[fn_Get_Date_Params]
    (
        @strPeriod varchar(254),
        @dtmStart_Date datetime,
        @dtmEnd_Date datetime,
        @dtmGetDate datetime,
        @intWhichOne int
    )

    RETURNS datetime

    AS

    */

    BEGIN

    DECLARE @dtmPeriodBegin datetime
    DECLARE @dtmPeriodEnd datetime
    DECLARE @intCurMonth int
    DECLARE @intCurYear int
    DECLARE @intCurDay int
    DECLARE @dtmDate datetime
    --------------------------------
    SET @intCurYear = right(Convert(varchar(10), @dtmGetDate,101),4)
    SET @intCurMonth = left(Convert(varchar(10), @dtmGetDate,101),2)
    SET @intCurDay = DATEPART(dw, @dtmGetDate)

    ------------------------------
    IF @strPeriod = 'Yesterday'
      BEGIN
      
       SET @dtmPeriodBegin = CONVERT(VARCHAR(10), ( @dtmGetDate - 1 ), 101)
       SET @dtmPeriodEnd = CONVERT(VARCHAR(10), ( @dtmGetDate ), 101)
      END
    ------------------------------
    IF @strPeriod = 'Last Full Weekday'
      BEGIN
       IF @intCurDay = 1
        BEGIN
          SET @dtmPeriodBegin = CONVERT(VARCHAR(10), ( @dtmGetDate - 2 ), 101)
          SET @dtmPeriodEnd = CONVERT(VARCHAR(10), ( @dtmGetDate ) - 1, 101)
        END
       IF @intCurDay = 2
        BEGIN
          SET @dtmPeriodBegin = CONVERT(VARCHAR(10), ( @dtmGetDate - 3 ), 101)
          SET @dtmPeriodEnd = CONVERT(VARCHAR(10), ( @dtmGetDate - 2 ), 101)
        END
       IF @intCurDay = 3
        BEGIN
          SET @dtmPeriodBegin = CONVERT(VARCHAR(10), ( @dtmGetDate - 1 ), 101)
          SET @dtmPeriodEnd = CONVERT(VARCHAR(10), ( @dtmGetDate ), 101)
        END
       IF @intCurDay = 4
        BEGIN
          SET @dtmPeriodBegin = CONVERT(VARCHAR(10), ( @dtmGetDate - 1 ), 101)
          SET @dtmPeriodEnd = CONVERT(VARCHAR(10), ( @dtmGetDate ), 101)
        END
       IF @intCurDay = 5
        BEGIN
          SET @dtmPeriodBegin = CONVERT(VARCHAR(10), ( @dtmGetDate - 1 ), 101)
          SET @dtmPeriodEnd = CONVERT(VARCHAR(10), ( @dtmGetDate ), 101)
        END
       IF @intCurDay = 6
        BEGIN
          SET @dtmPeriodBegin = CONVERT(VARCHAR(10), ( @dtmGetDate - 1 ), 101)
          SET @dtmPeriodEnd = CONVERT(VARCHAR(10), ( @dtmGetDate ), 101)
        END
       IF @intCurDay = 7
        BEGIN
          SET @dtmPeriodBegin = CONVERT(VARCHAR(10), ( @dtmGetDate - 1 ), 101)
          SET @dtmPeriodEnd = CONVERT(VARCHAR(10), ( @dtmGetDate ), 101)
        END

      END
    ------------------------------
    IF @strPeriod = 'Last Full Week'
        BEGIN
            IF @intCurDay = 1
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 7), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate), 101)
                END
            IF @intCurDay = 2
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 8), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 1), 101)
                END
            IF @intCurDay = 3
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 9), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 2), 101)
                END
            IF @intCurDay = 4
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 10), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 3), 101)
                END
            IF @intCurDay = 5
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 11), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 4), 101)
                END
            IF @intCurDay = 6
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 12), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 5), 101)
                END
            IF @intCurDay = 7
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 13), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 6), 101)
                END    
        END
    -------------------------------
    IF @strPeriod = 'Last Full Month'
        BEGIN
            IF @intCurMonth = 1
                BEGIN
                    SET @dtmPeriodBegin = str(12) + '/1/' + str(@intCurYear-1)
                    SET @dtmPeriodEnd = str(@intCurMonth) + '/1/' + str(@intCurYear)
                END
            ELSE
                BEGIN
                    SET @dtmPeriodBegin = str(@intCurMonth-1) + '/1/' + str(@intCurYear)
                    SET @dtmPeriodEnd = str(@intCurMonth) + '/1/' + str(@intCurYear)    
                END
        END
    ---------------------------------
    IF @strPeriod = 'Last Full Quarter'
        BEGIN
            IF @intCurMonth in (1)
                BEGIN
                    SET @dtmPeriodBegin = str(10) + '/1/' + str(@intCurYear-1)
                    SET @dtmPeriodEnd = str(@intCurMonth) + '/1/' + str(@intCurYear)
                END
            IF @intCurMonth in (2)
                BEGIN
                    SET @dtmPeriodBegin = str(10) + '/1/' + str(@intCurYear-1)
                    SET @dtmPeriodEnd = str(@intCurMonth-1) + '/1/' + str(@intCurYear)
                END
            IF @intCurMonth in (3)
                BEGIN
                    SET @dtmPeriodBegin = str(10) + '/1/' + str(@intCurYear-1)
                    SET @dtmPeriodEnd = str(@intCurMonth-2) + '/1/' + str(@intCurYear)
                END
            IF @intCurMonth in (4,7,10)
                BEGIN
                    SET @dtmPeriodBegin = str(@intCurMonth-3) + '/1/' + str(@intCurYear)
                    SET @dtmPeriodEnd = str(@intCurMonth) + '/1/' + str(@intCurYear)    
                END
            IF @intCurMonth in (5,8,11)
                BEGIN
                    SET @dtmPeriodBegin = str(@intCurMonth-4) + '/1/' + str(@intCurYear)
                    SET @dtmPeriodEnd = str(@intCurMonth-1) + '/1/' + str(@intCurYear)    
                END
            IF @intCurMonth in (6,9,12)
                BEGIN
                    SET @dtmPeriodBegin = str(@intCurMonth-5) + '/1/' + str(@intCurYear)
                    SET @dtmPeriodEnd = str(@intCurMonth-2) + '/1/' + str(@intCurYear)    
                END
        END
    ---------------------------------
    if @strPeriod = 'Last 7 Days'
        BEGIN
            SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 7), 101)
            SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate), 101)
        END

    -------------------------------------------
    IF @strPeriod = 'Last Full 18 Months'
                
    --10/23/06 Per Tevor, End Date always needs to include the Friday before the report is run to get all the previous weeks' postings.
        BEGIN
            IF @intCurDay = 1 --Sunday
                        BEGIN
                            SET @dtmPeriodEnd = CONVERT( varchar(10), (@dtmGetDate - 1), 101)--@dtmGetDate - 8
                            SET @dtmPeriodBegin = dateadd (mm, -18, @dtmperiodend)
                        END
                IF @intCurDay = 2 --Monday
                        BEGIN
                            SET @dtmPeriodEnd = CONVERT( varchar(10), (@dtmGetDate - 2), 101)--@dtmGetDate - 2
                            SET @dtmPeriodBegin = dateadd (mm, -18, @dtmperiodend)
                        END
                IF @intCurDay = 3 --Tuesday
                        BEGIN
                            SET @dtmPeriodEnd = CONVERT( varchar(10), (@dtmGetDate - 3), 101)--@dtmGetDate- 3
                            SET @dtmPeriodBegin = dateadd (mm, -18, @dtmperiodend)
                        END
                IF @intCurDay = 4 --Wednesday
                        BEGIN
                            SET @dtmPeriodEnd = CONVERT( varchar(10), (@dtmGetDate - 4), 101)--@dtmGetDate - 4
                            SET @dtmPeriodBegin = dateadd(mm, -18, @dtmperiodend)
                        END
                IF @intCurDay = 5 --Thursday
                        BEGIN
                            SET @dtmPeriodEnd = CONVERT( varchar(10), (@dtmGetDate - 5), 101) --@dtmGetDate - 5
                            SET @dtmPeriodBegin = dateadd (mm, -18, @dtmperiodend)
                        END
                IF @intCurDay =6 --Friday
                        BEGIN
                            SET @dtmPeriodEnd = CONVERT( varchar(10), (@dtmGetDate - 6), 101)--@dtmGetDate - 6
                            SET @dtmPeriodBegin = dateadd (mm, -18, @dtmperiodend)
                        END
                IF @intCurDay = 7 --Saturday
                        BEGIN
                            SET @dtmPeriodEnd = CONVERT( varchar(10), (@dtmGetDate - 7), 101)--@dtmGetDate - 7
                            SET @dtmPeriodBegin = dateadd (mm, -18, @dtmperiodend)
                        END
        END
    ---------------------------------
    --Calculates one year from the prior month of the run date----
    ---------------------------------
    IF @strPeriod = 'Last Full Year'
        BEGIN
            IF @intCurMonth = 1
                BEGIN
                    SET @dtmPeriodEnd = str(1) + '/1/' + str(@intCurYear)
                    SET @dtmPeriodBegin = str(@intCurMonth) + '/1/' + str(@intCurYear-1)
                END
            ELSE
                BEGIN
                    SET @dtmPeriodBegin = str(@intCurMonth ) + '/1/' + str(@intCurYear-1)
                    SET @dtmPeriodEnd = str(@intCurMonth) + '/1/' + str(@intCurYear)    
                END
        END
    ---------------------------------
    IF @strPeriod = 'Last Full Calendar Year'
        BEGIN    
            SET @dtmPeriodBegin = str(1) + '/1/' + str(@intCurYear-1)
            SET @dtmPeriodEnd = str(1) + '/1/' + str(@intCurYear)    
        END
    ---------------------------------
    IF @strPeriod = 'Last Full Fiscal Year'
        BEGIN
            IF @intCurMonth > 6
                BEGIN
                    SET @dtmPeriodBegin = str(7) + '/1/' + str(@intCurYear-1)
                    SET @dtmPeriodEnd = str(7) + '/1/' + str(@intCurYear)
                END
            ELSE
                BEGIN
                    SET @dtmPeriodBegin = str(7) + '/1/' + str(@intCurYear-2)
                    SET @dtmPeriodEnd = str(7) + '/1/' + str(@intCurYear-1)
                END
        END    
    ---------------------------------
    IF @strPeriod = 'Current Calendar Year To Date'    
        BEGIN
        
                    SET @dtmPeriodBegin = str(1) + '/1/' + str(@intCurYear)
                    SET @dtmPeriodEnd = CONVERT( varchar(10), (@dtmGetDate + 1), 101)
        END
    ---------------------------------
    IF @strPeriod = 'Current Month To Date'    
        BEGIN
        
                    SET @dtmPeriodBegin = str(@intCurMonth) + '/1/' + str(@intCurYear)
                    SET @dtmPeriodEnd = CONVERT( varchar(10), (@dtmGetDate + 1), 101)
        END
    ---------------------------------------------
    IF @strPeriod = 'Current Fiscal Year to Date'
        BEGIN
            IF @intCurMonth > 6
                BEGIN
                    SET @dtmPeriodBegin = str(7) + '/1/' + str(@intCurYear)
                    SET @dtmPeriodEnd = CONVERT( varchar(10), (@dtmGetDate + 1), 101)
                END
            ELSE
                BEGIN
                    SET @dtmPeriodBegin = str(7) + '/1/' + str(@intCurYear-1)
                    SET @dtmPeriodEnd = CONVERT( varchar(10), (@dtmGetDate + 1), 101)
                    
                END
        END
    ---------------------------------
    IF @strPeriod = 'Curr Fisc Yr Thru Last Full Mo'        
        BEGIN
            IF @intCurMonth > 6
                BEGIN
                    SET @dtmPeriodBegin = str(7) + '/1/' + str(@intCurYear)
                    SET @dtmPeriodEnd = str(@intCurMonth) + '/1/' + str(@intCurYear)    
                END
            ELSE
                BEGIN
                    SET @dtmPeriodBegin = str(7) + '/1/' + str(@intCurYear-1)
                    SET @dtmPeriodEnd = str(@intCurMonth) + '/1/' + str(@intCurYear)    
                END
        END
    -- ---------------------------------
    If @strPeriod = 'Use Custom Period'
        BEGIN
            SET @dtmPeriodBegin = LEFT (CONVERT(VARCHAR(10),@dtmStart_Date,101),10)
            SET @dtmPeriodEnd = LEFT(CONVERT(VARCHAR(10), @dtmEnd_Date + 1,101),10)
        END
    ---------------------------------

    if @strPeriod = 'Next 7 Days'
        BEGIN
            SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate + 1), 101)
            SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate + 7), 101)
        END
    ------------------------------
    IF @strPeriod = 'Next Full Month'
        BEGIN
            IF @intCurMonth = 12
                BEGIN
                    SET @dtmPeriodBegin = str(1) + '/1/' + str(@intCurYear+1)
                    SET @dtmPeriodEnd = str(2) + '/1/' + str(@intCurYear+1)
                END
            ELSE IF @intCurMonth = 11
                BEGIN
                    SET @dtmPeriodBegin = str(@intCurMonth+1) + '/1/' + str(@intCurYear)
                    SET @dtmPeriodEnd = str(1) + '/1/' + str(@intCurYear+1)
                END
            ELSE
                BEGIN
                    SET @dtmPeriodBegin = str(@intCurMonth + 1) + '/1/' + str(@intCurYear)
                    SET @dtmPeriodEnd = str(@intCurMonth+2) + '/1/' + str(@intCurYear)    
                END
        END

    --------------------------------------------------------------------------
    /*
    Used to identify the start and end date of the two prio completed weeks, where a week is defined as Sunday
    through Saturday.
    */
    IF @strPeriod = 'Last Full 2 Weeks'
        BEGIN
            IF @intCurDay = 1 --Sunday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 14), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate), 101)
                END
            IF @intCurDay = 2 --Monday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 15), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 1), 101)
                END
            IF @intCurDay = 3 --Tuesday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 16), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 2), 101)
                END
            IF @intCurDay = 4 --Wedensday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 17), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 3), 101)
                END
            IF @intCurDay = 5 --Thursday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 18), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 4), 101)
                END
            IF @intCurDay = 6 --Friday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 19), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 5), 101)
                END
            IF @intCurDay = 7 --Saturday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 20), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 6), 101)
                END    
        END
    --------------------------------------------------------------------------
    /*
    Used to identify the date of the last Frinday.
    */
    IF @strPeriod = 'Last Friday'
        BEGIN
            IF @intCurDay = 1 --Sunday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 2), 101)
                END
            IF @intCurDay = 2 --Monday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 3), 101)
                END
            IF @intCurDay = 3 --Tuesday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 4), 101)
                END
            IF @intCurDay = 4 --Wedensday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 5), 101)
                END
            IF @intCurDay = 5 --Thursday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 6), 101)
                END
            IF @intCurDay = 6 --Friday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 7), 101)
                END
            IF @intCurDay = 7 --Saturday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 1), 101)
                END    
        END
    -------------------------------
    IF @strPeriod = 'Last Full 12 Months'
        BEGIN
            SET @dtmPeriodEnd = str(@intCurMonth) + '/1/' + str(@intCurYear)
            SET @dtmPeriodBegin = dateadd (mm, -12, @dtmperiodend)
        END
    ------------------------------
    IF @strPeriod = 'Last Full 6 Months'
        BEGIN
            SET @dtmPeriodEnd = str(@intCurMonth) + '/1/' + str(@intCurYear)
            SET @dtmPeriodBegin = dateadd (mm, -6, @dtmperiodend)
        END
    ------------------------------
    IF @strPeriod = 'Prior Last Full Week'
        BEGIN
            IF @intCurDay = 1 --Sunday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 14), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 7), 101)
                END
            IF @intCurDay = 2 --Monday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 15), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 8), 101)
                END
            IF @intCurDay = 3 --Tuesday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 16), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 9), 101)
                END
            IF @intCurDay = 4 --Wednesday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 17), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 10), 101)
                END
            IF @intCurDay = 5 --Thursday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 18), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 11), 101)
                END
            IF @intCurDay = 6 --Friday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 19), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 12), 101)
                END
            IF @intCurDay = 7 --Saturday
                BEGIN
                    SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 20), 101)
                    SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate - 13), 101)
                END    
        END
    ---------------------------------
    IF @strPeriod = 'Current Calendar Year To Last Full Month'    
        BEGIN
            SET @dtmPeriodBegin = str(1) + '/1/' + str(@intCurYear)
            SET @dtmPeriodEnd = str(@intCurMonth) + '/1/' + str(@intCurYear)
        END
    ------------------------------
    IF @strPeriod = 'Last 365 Days'
        BEGIN
            SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 365), 101)
            SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate), 101)
        END
    ------------------------------
    if @strPeriod = 'Prior 90 Days'
        BEGIN
            SET @dtmPeriodBegin = Convert(varchar(10),(@dtmGetDate - 90), 101)
            SET @dtmPeriodEnd = Convert(varchar(10),(@dtmGetDate + 1), 101)
        END

    ------------------------------
    IF @strPeriod = 'Yesterday From Business Day'
      BEGIN
       IF @intCurDay = 1
        BEGIN
          SET @dtmPeriodBegin = CONVERT(VARCHAR(10), ( @dtmGetDate - 2 ), 101)
          SET @dtmPeriodEnd = CONVERT(VARCHAR(10), ( @dtmGetDate ) - 1, 101)
        END
       IF @intCurDay = 2
        BEGIN
          SET @dtmPeriodBegin = CONVERT(VARCHAR(10), ( @dtmGetDate - 3 ), 101)
          SET @dtmPeriodEnd = CONVERT(VARCHAR(10), ( @dtmGetDate ), 101)
        END
       IF @intCurDay = 3
        BEGIN
          SET @dtmPeriodBegin = CONVERT(VARCHAR(10), ( @dtmGetDate - 1 ), 101)
          SET @dtmPeriodEnd = CONVERT(VARCHAR(10), ( @dtmGetDate ), 101)
        END
       IF @intCurDay = 4
        BEGIN
          SET @dtmPeriodBegin = CONVERT(VARCHAR(10), ( @dtmGetDate - 1 ), 101)
          SET @dtmPeriodEnd = CONVERT(VARCHAR(10), ( @dtmGetDate ), 101)
        END
       IF @intCurDay = 5
        BEGIN
          SET @dtmPeriodBegin = CONVERT(VARCHAR(10), ( @dtmGetDate - 1 ), 101)
          SET @dtmPeriodEnd = CONVERT(VARCHAR(10), ( @dtmGetDate ), 101)
        END
       IF @intCurDay = 6
        BEGIN
          SET @dtmPeriodBegin = CONVERT(VARCHAR(10), ( @dtmGetDate - 1 ), 101)
          SET @dtmPeriodEnd = CONVERT(VARCHAR(10), ( @dtmGetDate ), 101)
        END
       IF @intCurDay = 7
        BEGIN
          SET @dtmPeriodBegin = CONVERT(VARCHAR(10), ( @dtmGetDate - 1 ), 101)
          SET @dtmPeriodEnd = CONVERT(VARCHAR(10), ( @dtmGetDate ), 101)
        END
      END

        ------------------------------
    --Here is where the UDF determines which date date should be returned to the SP
    IF @intWhichOne = 1
        SET @dtmDate = @dtmPeriodBegin
    ELSE
        SET @dtmDate = @dtmPeriodEnd

    RETURN
        @dtmDate

    END

    GO

    The SP code I included in my original post was a lot dumb-ed down version of the actual code, sorry for not mentioning that before.  There are additional tables involved in the FROM clause, and the join between the main table (which I denoted as MyTable) and another is showing up in the Execution Plan as a potential issue, regardless of how the parameters are used in the WHERE clause.  We use a proprietary Vendor supplied database, so that was my reason for not sharing the actual SP code.  I'll do some additional reading on the parameter sniffing to educate myself there.  The approach we're using here (getting some parameters from a report to determine the reporting period to be used) is one I've used for many years without seeing these types of performance issues.  Granted I'm now using on the same vendor supplied database, although at a different organization, with potentially different SQL Server configurations, indexes, etc.

    This function can be completely re-written and optimized to run as an iTVF. I'll try to cook something up later tonight.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Thanks Alan.
    In doing some additional testing, and reading up on parameter sniffing (https://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/), I'm finding that the following code addition makes a significant performance improvement (5 minutes to 20 seconds) for the SP.
    I haven't used this technique before, and don't fully understand the implications for DateTime columns, so this might not be a good long term solution, but thought I would share my findings.


    OPTION (OPTIMIZE FOR (@Report_Start_Date = '1/1/2019', @Report_End_Date = '1/1/2019'));

  • dukesbrent - Monday, February 25, 2019 4:45 PM

    Thanks Alan.
    In doing some additional testing, and reading up on parameter sniffing (https://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/), I'm finding that the following code addition makes a significant performance improvement (5 minutes to 20 seconds) for the SP.
    I haven't used this technique before, and don't fully understand the implications for DateTime columns, so this might not be a good long term solution, but thought I would share my findings.


    OPTION (OPTIMIZE FOR (@Report_Start_Date = '1/1/2019', @Report_End_Date = '1/1/2019'));

    What you've done is tell the optimizer to use a specific value for creating the execution plan. Before, as was said earlier, you were using local variables. These lead the optimizer to use an average of the statistics, rather than specific values. OPTIMIZE FOR changes what the optimizer does. This is a fine approach to the problem assuming those values are accurate and remain so for the rest of the data. The key is to look at the estimated rows that the optimizer is using to create the execution plan. Are these accurate to the rows in the table or not? If they are, great. If they're not, that's going to lead to performance problems as the plan doesn't reflect reality.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply