Slow running Stored Procedure when using Date Parameters

• dukesbrent

  SSC Enthusiast

  Points: 192

  More actions

  February 25, 2019 at 8:56 am

  #381558

  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

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  February 25, 2019 at 9:25 am

  #2022858

  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

• Jonathan AC Roberts

  SSCoach

  Points: 18407

  More actions

  February 25, 2019 at 9:35 am

  #2022859

  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)

• jcelko212 32090

  SSCrazy Eights

  Points: 9304

  More actions

  February 25, 2019 at 11:07 am

  #2022872

  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. 

• Chris Harshman

  SSC-Forever

  Points: 42192

  More actions

  February 25, 2019 at 12:40 pm

  #2022896

  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/

• dukesbrent

  SSC Enthusiast

  Points: 192

  More actions

  February 25, 2019 at 2:47 pm

  #2022923

  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.

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  February 25, 2019 at 3:32 pm

  #2022928

  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

• dukesbrent

  SSC Enthusiast

  Points: 192

  More actions

  February 25, 2019 at 4:45 pm

  #2022930

  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'));


• Grant Fritchey

  SSC Guru

  Points: 399061

  More actions

  February 26, 2019 at 2:33 am

  #2022943

  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