Azure Synapse无服务器SQL池SQL函数大数据集性能优化求助

索引问题确认 #

Azure Synapse无服务器SQL池不支持创建传统索引，性能优化主要依赖底层存储的文件组织（如分区、分桶）和统计信息。所以你的性能瓶颈确实来自查询本身的低效写法，而非缺失索引。

原函数的核心性能问题 #

原函数的写法存在几个致命的性能损耗点：

• 对my_db_name.price表进行了多次重复扫描（每个子查询都会单独扫一遍），大数据集下IO开销呈倍数增长
• 嵌套CASE WHEN+多层子查询的结构，导致查询优化器无法生成高效执行计划
• 多余的DISTINCT和重复的CAST(start_date AS DATE)操作（如果start_date本身是DATE类型，这步完全冗余）

重写后的高效函数 #

CREATE FUNCTION my_db_name.get_price_at_mo_order
    (@Material_Id VARCHAR(20), 
     @MO_Date DATE)
RETURNS table AS  
RETURN 
(
    WITH price_cte AS (
        SELECT 
            material_id,
            price,
            start_date,
            -- 按逻辑计算目标日期：优先取<=订单日期的最新价格日期，无匹配则取最早日期
            COALESCE(
                MAX(CASE WHEN start_date <= @MO_Date THEN start_date END) OVER (PARTITION BY material_id),
                MIN(start_date) OVER (PARTITION BY material_id)
            ) AS target_date
        FROM my_db_name.price
        WHERE material_id = @Material_Id
    )
    SELECT 
        pc.price, 
        m.product_code 
    FROM price_cte pc
    LEFT JOIN my_db_name.material m ON pc.material_id = m.material_id
    WHERE pc.start_date = pc.target_date
);

优化逻辑说明 #

1. 单次扫描表：通过CTE+窗口函数，只扫描price表一次就完成目标日期的计算，彻底消除多次重复扫描的开销
2. 简化条件判断：用COALESCE替代嵌套CASE，逻辑和原函数完全一致，但写法更简洁，优化器更容易处理：
   • 先筛选出当前物料所有早于/等于订单日期的起始日期，取最大值
   • 如果没有符合条件的日期（订单日期早于所有价格生效日期），则取该物料最早的价格生效日期
3. 去除冗余操作：去掉不必要的DISTINCT和重复转换，减少计算开销

额外性能建议 #

• 给price表按material_id和start_date做分区存储（比如按年月分区），无服务器SQL池会自动利用分区裁剪减少扫描范围
• 定期更新统计信息：执行UPDATE STATISTICS my_db_name.price;，帮助优化器生成更优的执行计划
• 如果该函数是被大表关联调用，确保它是内联表值函数（你的函数已经满足），避免逐行执行的性能损耗

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