Hello.
I have a function that will compare a date and if it is between 6 to 6 date it will set the date to current date:
ALTER FUNCTION [dbo].[fnCinemaDate] (@InDate DateTime)
RETURNS Date
AS
BEGIN
DECLARE @CDate Date
SET @CDate = @InDate
if Datepart(hh, @InDate) < 6 SET @CDate = DATEADD(d, -1, @CDate)
RETURN @CDate
ENDI am wondering if there is a better way to do that because as I see this is a non sargable function and is slowing my queries down a lot.
Thanks.
Converting it to a table valued function is all, I think?, that you can do. But there will be code changes required if you do that ...
I think adding WITH SCHEMABINDING also helps:
...
RETURNS Date
WITH SCHEMABINDING
AS
...
Pity there isn't a pre-processor (which would just make the substitution at compile time). If you can live with it you could build a pre-processor that did that job - probably too much of a pain to run your code through a pre-processor before creating SProcs etc. in DEV, but you could make that substitution as you deploy to TEST (and tehreafter to PRODUCTION etc.) - i.e. using a RegEx something like this to replace
dbo.fnCinemaDate\(([^)]+)\)
with
CASE WHEN Datepart(Hour, $1) < 6 THEN DATEADD(Day, -1, $1) ELSE $1 END
I'm not sure how to use a regex with an sql function, I haven't seen that ever.
Also I am trying like this but I can't get any results. IT gives me:
The datepart hour is not supported by date function datepart for data type date.
ALTER FUNCTION [dbo].[fnCinemaDateX] (@InDate DateTime)
RETURNS Date
WITH SCHEMABINDING
AS
BEGIN
DECLARE @CDate Date
SET @CDate = @InDate
return CASE WHEN Datepart(Hour, @CDate) < 6 THEN DATEADD(Day, -1, @CDate) ELSE @CDate END
END
GOI guess it must be like this:
return CASE WHEN Datepart(hh, @InDate) < 6 THEN DATEADD(Day, -1, @CDate) ELSE @CDate END
Is this sargable?
Thanks
The problem is that if i run it like this:
and (OrderH_dtmInitiated) >= @DateFrom
and (OrderH_dtmInitiated) <= @DateTo
It executes in 1 second but if i run it like this:
and dbo.fnCinemaDateX (OrderH_dtmInitiated) >= @DateFrom
and dbo.fnCinemaDateX (OrderH_dtmInitiated) <= @DateToIt takes 28 seconds!
This seems to work but it takes almost the same time as the function:
select C.COUPON,
OH.OrderH_strCinemaId as CinemaId,
TH.OrderTH_intSessionId as PerformanceId,
C.DATEUPDATED AS DateCreated
FROM VRVISTASQL.VSAPI.dbo.COUPONS C WITH(NOLOCK)
inner join Vrvistasql.vistait.dbo.tblorderhistory OH on OH.orderH_struserid = C.UserSessionid
inner join Vrvistasql.vistait.dbo.tblOrderTicketHistory TH on OH.OrderH_intID = TH.OrderH_intID
WHERE C.[STATUS] = 2
--and convert(date, OrderH_dtmInitiated +6) >= @DateFrom
and @DateFrom <=
CASE WHEN Datepart(hh, OrderH_dtmInitiated) < 6 THEN DATEADD(Day, -1, OrderH_dtmInitiated) ELSE convert(date,OrderH_dtmInitiated) END
and @DateTo >=
CASE WHEN Datepart(hh, OrderH_dtmInitiated) < 6 THEN DATEADD(Day, -1,OrderH_dtmInitiated) ELSE convert(date,OrderH_dtmInitiated) ENDSorry, that would not be in SQL, it would have to be an external process that converted your code - i.e. replaced any Function Calls with hard-wired equivalent. That would give you maximum performance, and remove Function calls (but you could still WRITE the code with function calls, and use that during DEV, for convenience, you'd just run the "Converter" before deploying the code to Testing / Production)
Nope. No function is going to be SARGable. A Table Valued Function will most likely perform OK, or "not so bad as to be useless". So you could try converting your Scalar function to Table Valued.
You can slim your Scalar a bit, but i still don't think it will run at an acceptable speed:
ALTER FUNCTION [dbo].[fnCinemaDateX]
(
@InDate DateTime
)
RETURNS Date
WITH SCHEMABINDING
AS
BEGIN
RETURN CASE WHEN DATEPART(Hour, @InDate) < 6 THEN DATEADD(Day, -1, @InDate) ELSE @InDate END
END
That's not SARGable either 
Any function, acting on a column, is likely to prevent indexes being used, so this bit for example:
CASE WHEN Datepart(hh, OrderH_dtmInitiated) < 6
here the function being used is DatePart
You could "split" the [OrderH_dtmInitiated] into various parts, in separate columns in the table, so that you can query them without having to use a function to extract the Hour from the Date, or you could have a column that was the "Modified Date" - i.e. already processed so as to yesterday-if-before-6am, otherwise-today (and converted from DateTime to Date datatype)
You might get away with just adding a Computed Column to achieve that (and index it so it is available to your Query)
Hi.
Can you explain the computed column you are talking about?
What I have found right now, is adding everything to a temp table (with expanded dates, so I do not miss any data) with an index and then running the function into the temp table.
That will do about A second or so from the 30 seconds that will do if i run the function straight up.
But is this a correct approach?
declare @DateFromX as datetime
declare @DateToX as datetime
declare @DateFrom as datetime
declare @DateTo as datetime
set @DateFrom = '20170125'
set @DateTo = '20170127'
set @DateFromX = DateAdd(day, -1, @DateFrom)
set @DateToX = DateAdd(day, 2, @DateTo)
select C.COUPON,
OH.OrderH_strCinemaId as CinemaId,
TH.OrderTH_intSessionId as PerformanceId,
C.DATEUPDATED AS DateCreated,OrderH_dtmInitiated
into #tempUP
FROM VRVISTASQL.VSAPI.dbo.COUPONS C WITH(NOLOCK)
inner join Vrvistasql.vistait.dbo.tblorderhistory OH on OH.orderH_struserid = C.UserSessionid
inner join Vrvistasql.vistait.dbo.tblOrderTicketHistory TH on OH.OrderH_intID = TH.OrderH_intID
WHERE C.[STATUS] = 2
and (OrderH_dtmInitiated) >= @DateFromX
and (OrderH_dtmInitiated) <= @DateToX
--group by C.COUPON,OH.OrderH_strCinemaId,TH.OrderTH_intSessionId, C.DATEUPDATED
CREATE CLUSTERED INDEX Tmp_Upstream_IDX_C_Users_UserID ON #tempUP(COUPON)
select COUPON,CinemaId, PerformanceId, DateCreated from #tempUP
where dbo.fnCinemaDateX (OrderH_dtmInitiated) >= @DateFrom
and dbo.fnCinemaDateX (OrderH_dtmInitiated) <= @DateTo
group by COUPON,CinemaId, PerformanceId, DateCreated --, OrderH_dtmInitiated
drop table #tempUPThanks.