SQL a Function under Select make it very Slow

Question

I am doing a SQL select query. Under the [E] field , there is a stored function called "[defaultDB].[dbo].HighFirst" it is to judge "high" or "low" happen first.

Unfortunately, this judgment function makes the query super slow. It takes only 2 sec to complete the query if I skip the function (replace the function with 1). However, it takes over 30 mins if I use the function. Is it possible not to use the store function and directly embed the judgment into the query?

USE defaultDB; 
IF OBJECT_ID (N'[defaultDB].[dbo].[HighFirst]', N'FN') IS NOT NULL  
    DROP FUNCTION HighFirst;  
GO  

CREATE FUNCTION HighFirst(@Date INT, @Stime INT, @ETime INT, @High Float, @Low Float)  
RETURNS BIT
AS   
BEGIN  
    Declare @result Bit
        IF          
            (SELECT min([time]) FROM [defaultDB].[dbo].[Table2]    WHERE [date] = @Date AND [Start] >= @Stime AND [Time]<= @etime and [H] > @high) <=   (SELECT min([time]) FROM [defaultDB].[dbo].[Table2]    WHERE [date] = @Date AND [Start] >= @Stime AND [Time]<= @etime and [L] < @Low)
            Set @result = 1
        ELSE 
            Set @result = 0; 
        Return @result  
END; 



Select 
 [Stime], 
 [Etime],
 [target],
 [CL],
 [WinRate] = count(CASE WHEN [Max] > [target]  THEN 1 END)  / cast( count(*) as float),
  [E] = AVG( Case 
    WHEN [Max] >= [target] AND [Min]< [CL] THEN IIF ([defaultDB].[dbo].HighFirst([date], [Stime], [Etime], [Target], [CL]) =1 , [Target] ,[CL])         
    WHEN [Max] >= [target] AND [Min] > [CL] THEN [target]  /*HIT*/
    WHEN [Max] < [target] AND [Min] < [CL] THEN [CL] /*CL*/
    WHEN [Max] < [target] AND [Min] > [CL] THEN [EndReturn] /*both not */
 END ) , 
 [count] = count(*)
 FROM [defaultDB].[dbo].[Table1] 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  [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  
  
  Group By [Stime], [Etime], [Target], [CL]

If you turn your function into an inline table value function, and reference in the `FROM` (via an `APPLY`) I suspect the performance will be much better. — Thom A, Aug 27 '21 at 16:34
Side notes: `JOIN ON 1=1` is silly, just write `CROSS JOIN`. I hope `Table1.date` is not an `int` column, that is just weird — Charlieface, Aug 27 '21 at 16:53

Serg · Answer 1 · 2021-08-27T16:41:23.167

Following the pattern of function usage you can try to redifine the function to return the value instead of flag so you needn't IIF check. You can do it with conditional aggregation. This way [defaultDB].[dbo].[Table2] is scanned only once:

CREATE FUNCTION HighFirstVal(@Date INT, @Stime INT, @ETime INT, @High Float, @Low Float)  
RETURNS float
AS
BEGIN   
    RETURN (SELECT case when 
                min(case when [H] > @high then [time] end) <= min(case when [L] < @Low then [time] end) 
          then @high else @low end 
    FROM [defaultDB].[dbo].[Table2]    
    WHERE [date] = @Date AND [Start] >= @Stime AND [Time]<= @etime)
END

or you can use the subquery in RETURN in you query directly after replacing params with the columns.

score 0 · Accepted Answer · answered Aug 27 '21 at 16:38

I see two ways to optimize.

First way with minor changes is to transform your table2 to a memory table and then transforem your HighFirst to a native compiled function

the other way could be to use outer apply instead of function

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;

Thank you !!! AMAZING!!! it used to take more than one and half hour. Now it takes 1min28 sec!!! Is there any way to further increase the speed? — Chow Stanley, Aug 27 '21 at 17:06
After applying your suggestion, the sql query get 10 times faster . However it is still not fast enough. Therefore I tried to use GPU with blazing SQL. Unfortunately.... it does not support "APPLY" operator... How should i revise the sql? Should i use join? — Chow Stanley, Sep 03 '21 at 16:49

score 0 · Answer 3 · answered Aug 28 '21 at 20:39

You can transform this function into an inline Table Valued Function, this is likely to perform much better.

CREATE FUNCTION dbo.HighFirst
  (@Date INT, @Stime INT, @ETime INT, @High Float, @Low Float)  
RETURNS TABLE
AS RETURN

SELECT Result =
  CASE WHEN MIN(CASE WHEN t2.H > @high THEN t2.[time] END) >
            MIN(CASE WHEN t2.L < @Low  THEN t2.[time] END)
      THEN @High ELSE @Low END
FROM dbo.Table2 t2
WHERE t2.[date] = @Date
  AND t2.Start >= @Stime
  AND t2.[Time] <= @etime;

GO

You use it with APPLY like this:

Select 
 [Stime], 
 [Etime],
 [target],
 [CL],
 [WinRate] = count(CASE WHEN [Max] > [target]  THEN 1 END)  / cast( count(*) as float),
  [E] = AVG( Case 
    WHEN [Max] >= [target] AND [Min]< [CL] THEN h.Result
    WHEN [Max] >= [target] AND [Min] > [CL] THEN [target]  /*HIT*/
    WHEN [Max] < [target] AND [Min] < [CL] THEN [CL] /*CL*/
    WHEN [Max] < [target] AND [Min] > [CL] THEN [EndReturn] /*both not */
 END ) , 
 [count] = count(*)
 FROM [defaultDB].[dbo].[Table1] M
 CROSS APPLY [defaultDB].[dbo].HighFirst([date], [Stime], [Etime], [Target], [CL]) h

 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  [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  
  
  Group By [Stime], [Etime], [Target], [CL]

i believe the bottle neck is "function". I tried to apply the same sql query through direct way and put the sql query inside a function. Direct way is much faster — Chow Stanley, Sep 03 '21 at 16:46

SQL a Function under Select make it very Slow

3 Answers3

Linked