Further to my previous questions for speeding up the query, thanks to a_horse_with_no_name who rewrote my function to outer apply which made my query run 10 times faster already. However, the speed is still not fast enough for doing what i want.
Now, I am trying to use GPU for further speed up by using blazing SQL. Unfortunately, it returns "APPLY operator is not allowed under the current SQL conformance level" ... Would any body know how to rewrite the following code to join or whatever others than apply so i can give a trial to blazing SQL? I'll post the difference in speed after my trial.
SELECT IIF (TargetTime.MinTime <= CLTime.MinTime , [Target] ,[CL])
FROM [defaultDB].[dbo].[Table1] AS M
JOIN(VALUES(0.003), (0.0035), (0.004), (0.0045), (0.005), (0.0055), (0.006), (0.0065), (0.007), (0.0075), (0.008)) AS T([target])
ON 1 = 1
JOIN(VALUES(-0.003), (-0.0035), (-0.004), (-0.0045), (-0.005), (-0.0055), (-0.006), (-0.0065), (-0.007), (-0.0075), (-0.008), (-0.0085), (-0.009), (-0.0095), (-0.01), (-0.0105), (-0.011), (-0.0115)) AS C([CL])
ON 1 = 1
OUTER APPLY
(
SELECT MIN([time]) AS MinTime
FROM [defaultDB].[dbo].[Table2]
WHERE [date] = M.[DATE]
AND [Start] >= M.Stime
AND [Time] <= M.etime
AND [H] > T.target
) as TargetTime
OUTER APPLY
(
SELECT MIN([time]) AS MinTime
FROM [defaultDB].[dbo].[Table2]
WHERE [date] = M.[DATE]
AND [Start] >= M.Stime
AND [Time] <= M.etime
AND L < C.CL
) as CLTime
WHERE [M].[date] IN(20120307, 20120601, 20121109, 20130826, 20131002, 20140117, 20140122, 20140311, 20140529, 20140718, 20150619, 20151014, 20151022, 20160411, 20160516, 20160721, 20160818, 20160909, 20170127, 20170213, 20170921, 20171025, 20171229, 20180116, 20180315, 20180926, 20181022, 20181128, 20181211, 20190104, 20190329, 20190502, 20190521, 20190528, 20190611, 20190627, 20190823, 20190930, 20191104, 20191211, 20200214, 20200318, 20200529, 20200706, 20200828, 20201230, 20210112, 20210305, 20210318, 20210408, 20210525, 20210617, 20210625)
AND [Stime] >= 133000;
