子查询含常量时GROUP BY与DISTINCT的性能差异及调优问题

现象说明 #

我在子查询中引入常量is_issued = 1时，使用GROUP BY和DISTINCT会出现明显性能差异，若移除该常量，两个查询运行效率一致。

GROUP BY版本查询 #

该查询耗时超过15分钟，代码如下：

/*耗时10分钟以上*/
SELECT ld.[LNum],
       is_lease_insured = CAST(COALESCE(i.is_issued, 0) AS BIT)
FROM [dbo].[snapshot_ld] ld
    LEFT JOIN
    (
        SELECT LNum = value,
               i.snapshot_date,
               is_issued = 1
        FROM [dbo].[snapshot_ip] i
            CROSS APPLY STRING_SPLIT(i.LNum, ',')
        GROUP BY value,
                 i.snapshot_date
    ) i
        ON ld.[LNum] = i.LNum
           AND ld.snapshot_date = i.snapshot_date
WHERE ld.RowIsDeleted = 0
      AND ld.bimonthly_snapshot_date_flag = 1;

DISTINCT版本查询 #

逻辑与上述查询一致，耗时仅2秒，代码如下：

/*耗时2秒*/
SELECT ld.LNum,
       is_lease_insured = CAST(COALESCE(i.is_issued, 0) AS BIT)
FROM [dbo].[snapshot_ld] ld
    LEFT JOIN
    (
        SELECT DISTINCT
               lno = value, /*添加DISTINCT，移除GROUP BY*/
               i.snapshot_date,
               is_issued = 1
        FROM [dbo].[snapshot_ip] i
            CROSS APPLY STRING_SPLIT(i.LNum, ',')
    --GROUP BY value, i.snapshot_date
    ) i
        ON ld.LNum = i.lno
           AND ld.snapshot_date = i.snapshot_date
WHERE ld.RowIsDeleted = 0
      AND ld.bimonthly_snapshot_date_flag = 1;

疑问 #

目前不确定是直接选用性能更好的DISTINCT版本承担后续性能波动风险，还是有方案可以对GROUP BY版本进行调优，恳请大家提供相关建议。

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