sqlperformanceindexingspool

eliminate sql index spool time


I reviewed the execution plan of a sql query that have 3 index spool tasks which consume 69% (22% + 35% + 12%) time. My questions are ....

  1. is it possible to do the index spool before my request?
  2. Since the similar index spool may be done again very soon, is there any way to keep the result to eliminate the duplicated calculation?

The code is as follows.

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; 

Solution

  • By adding indexing, I speed up the query from 1:27 to 0: 12.