Azure Synapse Serverless通配符查询性能差异优化咨询

以下是几种可行的优化方法：

1. 创建分区外部表 #

利用ADLS的分区路径结构（pricedate=YYYY-MM-DD）创建外部表，让查询优化器直接识别分区，避免扫描所有通配符匹配的路径：

CREATE EXTERNAL DATA SOURCE adls
WITH (
    LOCATION = 'abfss://your-container@your-account.dfs.core.windows.net/',
    TYPE = HADOOP
);

CREATE EXTERNAL FILE FORMAT parquet_format
WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);

CREATE EXTERNAL TABLE dbo.daily_prices (
    col1 float,
    col2 float
)
PARTITIONED BY (pricedate date)
WITH (
    LOCATION = 'daily/',
    DATA_SOURCE = adls,
    FILE_FORMAT = parquet_format
);

-- 加载分区元数据
ALTER EXTERNAL TABLE dbo.daily_prices REBUILD;

-- 查询指定分区
SELECT TOP 10 pricedate, col1, col2
FROM dbo.daily_prices
WHERE pricedate = '2022-10-31';

外部表会自动映射路径中的pricedate分区，查询时优化器直接定位到对应分区的文件，性能和直接指定路径一致。

2. 使用动态SQL生成精确路径 #

如果不想创建外部表，可以用动态SQL在运行时拼接出精确的文件路径，等价于直接指定路径的查询：

DECLARE @pricedate DATE = '2022-10-31';
DECLARE @sql NVARCHAR(MAX);

SET @sql = N'
SELECT TOP 10 CAST(CONVERT(DATETIME, ''' + CONVERT(NVARCHAR(10), @pricedate, 120) + ''', 120) AS DATE) AS pricedate, col1, col2
FROM OPENROWSET(
    BULK ''daily/pricedate=' + CONVERT(NVARCHAR(10), @pricedate, 120) + '/*.parquet'',
    DATA_SOURCE=''adls'',
    FORMAT = ''parquet''
) WITH (
    col1 float,
    col2 float
) AS [result]';

EXEC sp_executesql @sql;

这种方式完全复用直接路径查询的高性能，同时保留动态指定日期的灵活性。

3. 强制优化器使用分区裁剪 #

如果坚持使用通配符+filepath(1)的写法，可以尝试通过OPTION(RECOMPILE)让优化器在运行时根据WHERE条件生成最优执行计划，避免预编译时的路径扫描：

SELECT TOP 10 CAST(CONVERT(DATETIME, result.filepath(1), 120) AS DATE) AS pricedate, col1, col2
FROM OPENROWSET(
    BULK 'daily/pricedate=*/*.parquet',
    DATA_SOURCE='adls',
    FORMAT = 'parquet'
) WITH (
    pricedate date,
    col1 float,
    col2 float
) AS [result]
WHERE result.filepath(1) = '2022-10-31'
OPTION(RECOMPILE);

RECOMPILE会让优化器重新评估执行计划，识别出只需要扫描指定日期的分区路径，而非所有匹配通配符的路径。

4. 检查并更新统计信息 #

确保SQL Server对外部数据源的路径分布有准确的统计信息，手动创建统计信息可以帮助优化器做出更好的决策：

如果使用外部表：

CREATE STATISTICS stats_daily_prices_pricedate
ON dbo.daily_prices (pricedate)
WITH FULLSCAN;

如果坚持使用OPENROWSET，可以先创建视图再生成统计信息：

CREATE VIEW vw_daily_prices AS
SELECT 
    CAST(CONVERT(DATETIME, result.filepath(1), 120) AS DATE) AS pricedate,
    col1, col2
FROM OPENROWSET(
    BULK 'daily/pricedate=*/*.parquet',
    DATA_SOURCE='adls',
    FORMAT = 'parquet'
) WITH (
    col1 float,
    col2 float
) AS [result];

CREATE STATISTICS stats_vw_daily_prices_pricedate
ON vw_daily_prices (pricedate)
WITH FULLSCAN;

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