优化多语句表值函数与Cross Apply结合的性能问题

我有一个用于筛选最优移动路径的查询，逻辑是：先通过第一个表值函数获取源位置列表，再针对每个源位置调用第二个表值函数查找匹配的目标位置。该查询功能正常，但整体耗时长达数分钟，核心问题在于针对每个源位置调用的GetDestinations()是多语句表值函数。

我希望获取改写或优化该逻辑的建议，尽管将其改为内联表值函数可能是最优方案，但该操作难度大且会导致查询可读性极差，因此希望尽量避免。

补充信息 #

• 已小幅优化主查询，但性能瓶颈仍集中在GetDestinations()上，已附上主查询和GetDestinations()的执行计划。
• 场景细节：StorageLocation表包含2万行数据，GetSources()函数平均返回5千条记录，GetDestinations()每次仅返回少量结果，但需执行5千次。

查询代码 #

;WITH sources AS -- Get source locations
    (
        SELECT s.Id_StorageLocation,
                s.FillingRate,
                s.NumStockUnit,
                s.OperationRotation
        FROM Client.ufn_GetHousekeepingSourceLocations(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL) s 
        WHERE s.NumTaskDrop = 0
            AND s.NumTaskPick = 0 
            AND s.FillingRate <= @MaxFullSource
            AND NOT EXISTS (SELECT 1 FROM Shipping.PreStagingAreaStorageLocation a  WHERE a.Id_StorageLocation = s.Id_StorageLocation)
            AND (s.LastDropTime IS NULL OR DATEDIFF(HOUR, s.LastDropTime, GETDATE()) >= @MinHoursFromLastOperation)
    ),
    availableRoutes AS -- Get possible routes joining sources with every destination
    (
        SELECT 
            ROW_NUMBER() OVER (PARTITION BY s.Id_StorageLocation ORDER BY d.FillingRate DESC ) AS RouteRank
            ,s.Id_StorageLocation       AS Id_Source
            ,s.FillingRate              AS SourceFillRate
            ,s.NumStockUnit             AS SourceStockNum
            ,d.Id_StorageLocation       AS Id_Destination
            ,d.FillingRate              AS DestinationFillRate
            ,d.NumEmptyPositions        AS DestinationFreePos
        FROM sources s 
        CROSS APPLY Client.ufn_GetHousekeepingDestinationLocations(s.Id_StorageLocation, 'False', 'False') d
        WHERE d.NumTaskPick = 0 
            AND d.NumTaskDrop = 0
            AND d.FillingRate >= @MinFullDestination
            AND (d.LastPickTime IS NULL OR DATEDIFF(HOUR, d.LastPickTime, GETDATE()) >= @MinHoursFromLastOperation)
            AND s.FillingRate <= d.FillingRate
            AND d.OperationRotation = s.OperationRotation
    )
    SELECT
        m.RouteRank,
        m.Id_Source,
        m.SourceFillRate,
        m.SourceStockNum,
        m.Id_Destination,
        m.DestinationFillRate,
        m.DestinationFreePos
    FROM availableRoutes m
    WHERE m.RouteRank = 1
    ORDER BY m.SourceFillRate

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