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.