SQL查询SELECT下调用存储函数过慢，如何内嵌逻辑替代优化？

性能问题根因 #

• 你使用的是标量自定义函数，SQL Server执行这类函数时会逐行触发调用：每处理Table1的1行数据，就要单独执行2次Table2的查询求最小时间，数据量较大时会产生极多重复查询开销，这就是耗时飙升的核心原因。

优化方案：直接嵌入逻辑消除函数调用 #

不需要保留存储函数，我们可以把判断逻辑转化为OUTER APPLY关联查询，一次计算所有需要的判断结果，改写后的完整查询如下：

USE defaultDB;
GO

SELECT 
    M.[Stime], 
    M.[Etime],
    T.[target],
    C.[CL],
    [WinRate] = COUNT(CASE WHEN M.[Max] > T.[target] THEN 1 END)  / CAST(COUNT(*) AS FLOAT),
    [E] = AVG(CASE 
        WHEN M.[Max] >= T.[target] AND M.[Min] < C.[CL] THEN IIF(ISNULL(HF.first_high_time, 999999) <= ISNULL(HF.first_low_time, 999999), T.[target], C.[CL])         
        WHEN M.[Max] >= T.[target] AND M.[Min] > C.[CL] THEN T.[target]  /* 仅命中高点 */
        WHEN M.[Max] < T.[target] AND M.[Min] < C.[CL] THEN C.[CL] /* 仅命中止损 */
        WHEN M.[Max] < T.[target] AND M.[Min] > C.[CL] THEN M.[EndReturn] /* 两者都未命中 */
    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 1=1
-- 替代原函数的关联查询，一次计算当前行对应的高低点首次触发时间
OUTER APPLY (
    SELECT 
        MIN(CASE WHEN [H] > T.[target] THEN [time] END) AS first_high_time,
        MIN(CASE WHEN [L] < C.[CL] THEN [time] END) AS first_low_time
    FROM [defaultDB].[dbo].[Table2]
    WHERE [date] = M.[date] 
        AND [Start] >= M.[Stime] 
        AND [Time] <= M.[Etime]
) HF
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 M.[Stime] >= 133000  
GROUP BY M.[Stime], M.[Etime], T.[Target], C.[CL]

如果需要进一步提升查询速度，可以给Table2创建联合索引，降低关联查询的IO开销：

CREATE NONCLUSTERED INDEX IX_Table2_DateStart ON Table2 ([date], [Start]) INCLUDE ([Time], [H], [L])

内容的提问来源于stack exchange，提问作者Chow Stanley