SQLTeam.com | Weblogs | Forums

Make sargable function


#1

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
END

I 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.


#2

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

#3

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
GO

#4

I 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


#5

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) <= @DateTo

It takes 28 seconds!


#6

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) END

#7

Sorry, 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

#8

That's not SARGable either :frowning:

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)


#9

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 #tempUP

Thanks.