-1

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; 
The Impaler
  • 45,731
  • 9
  • 39
  • 76

1 Answers1

0

There is a parameter to disable such kind of joins.... Set allow-loop-joins to true then you can do all reference