Azure Synapse中小表与大表关联查询耗时过长的优化方案求助

问题根源 #

你的原查询使用INNER JOIN t2 on 1=1触发了全量笛卡尔积运算：Synapse需要将小表load_table的单行数据与t2的3000万行逐一关联，即使这只是为了附加一个固定值。这种操作会触发大量数据重分布（round robin分布下，大表数据分散在多个节点，小表数据需要广播到所有节点），加上3000万行的关联计算，导致耗时暴增。

替代方案 #

方案1：使用变量存储加载时间，直接附加到查询结果 #

先单独获取t2的加载时间存入变量，再将变量作为常量列查询t2，完全避免关联操作：

DECLARE @t2_load_time DATETIME;
SELECT @t2_load_time = load_time FROM load_table WHERE table = 't2';
SELECT @t2_load_time AS load_time, * FROM t2;

优势：变量只查询一次，后续仅对t2执行一次聚集列存储扫描，附加常量列几乎无额外开销，耗时与单独查询t2接近。

方案2：将加载时间作为子查询嵌入SELECT列表 #

如果不想用变量，可以直接把单行子查询作为列值，Synapse会自动优化为常量广播：

SELECT 
    (SELECT load_time FROM load_table WHERE table = 't2') AS load_time,
    * 
FROM t2;

优势：语法更简洁，无需变量声明，执行计划与方案1一致，仅扫描t2一次并附加固定值。

方案3：使用临时表存储加载时间（适合多表复用场景） #

如果需要为多个表附加加载时间，可以先将加载时间存入临时表，再通过CROSS JOIN关联（单行临时表会被Synapse优化为广播）：

CREATE TABLE #temp_load (load_time DATETIME);
INSERT INTO #temp_load SELECT load_time FROM load_table WHERE table = 't2';

SELECT tl.load_time, t2.* 
FROM #temp_load tl
CROSS JOIN t2;

DROP TABLE #temp_load;

优势：临时表可以复用加载时间，适合批量处理多个表的场景，同样避免了全量笛卡尔积的低效运算。

验证效果 #

以上方案都不会触发不必要的关联计算，仅需对t2执行一次聚集列存储扫描，耗时应该和单独执行SELECT * FROM t2接近（略长于单独查询，但不会达到小时级）。

内容的提问来源于stack exchange，提问作者veer kumar rathore