February 25, 2019 at 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
February 25, 2019 at 9:25 am
dukesbrent - Monday, February 25, 2019 8:56 AMI 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 < @ReportEndDateThis 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.
February 25, 2019 at 9:35 am
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)
February 25, 2019 at 11:07 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.
February 25, 2019 at 12:40 pm
dukesbrent - Monday, February 25, 2019 8:56 AMI 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 < @ReportEndDateThis 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/
February 25, 2019 at 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.
February 25, 2019 at 3:32 pm
dukesbrent - Monday, February 25, 2019 2:47 PMHere 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)
ENDEND
------------------------------
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 = @dtmPeriodEndRETURN
@dtmDateEND
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.
-- Itzik Ben-Gan 2001
February 25, 2019 at 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'));
February 26, 2019 at 2:33 am
dukesbrent - Monday, February 25, 2019 4:45 PMThanks 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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy