向临时表插入快但查询极慢？原因及性能优化方案

问题概述 #

向临时表#Operation插入30万行、30列数据仅需3秒，但执行SELECT * FROM #Operation耗时长达3分钟，已尝试创建索引但无明显效果，最终查询结果无需排序。

具体优化措施 #

1. 排查tempdb磁盘瓶颈 #

临时表默认存储在tempdb中，若tempdb所在磁盘I/O性能不足（如与其他业务数据库共用磁盘、磁盘读写速度慢），会直接拖慢查询速度。可通过以下方式确认：

• 查看磁盘的使用率、读写队列长度（Windows任务管理器或SQL Server性能监视器中查看）
• 检查SQL Server等待类型，若存在大量PAGEIOLATCH_*等待，说明磁盘I/O是瓶颈

2. 避免SELECT *，明确指定查询列 #

SELECT *会强制SQL Server读取临时表的所有数据页，即使你只需要部分列，还会导致无法利用覆盖索引。将查询改为明确指定需要的列，减少数据传输量：

SELECT 
    OperationId,
    OperationTypeId,
    BranchId,
    DateUpdated,
    OperationDate,
    StepCountTypeId,
    UserId,
    StatusTypeId,
    ChannelTypeId,
    Amount, 
    OperationPersonId, 
    CurrencyTypeId
FROM #Operation;

3. 手动创建临时表并优化数据类型 #

SELECT ... INTO #Operation会自动推断源表的数据类型，可能存在数据类型冗余（比如用了NVARCHAR(255)但实际只需要VARCHAR(50)）。手动创建临时表，指定更紧凑的数据类型，减少存储体积与I/O开销：

-- 先手动创建临时表，根据实际数据调整数据类型
CREATE TABLE #Operation (
    OperationId INT,
    OperationTypeId INT,
    BranchId INT,
    DateUpdated DATETIME2(0), -- 不需要毫秒级精度时用DATETIME2(0)替代DATETIME
    OperationDate DATETIME2(0),
    StepCountTypeId INT,
    UserId INT,
    StatusTypeId INT,
    ChannelTypeId INT,
    Amount DECIMAL(12,2), -- 根据实际金额范围调整精度
    OperationPersonId INT,
    CurrencyTypeId INT
);

-- 插入数据，同时优化WHERE子句避免索引失效
INSERT INTO #Operation (
    OperationId, OperationTypeId, BranchId, DateUpdated,
    OperationDate, StepCountTypeId, UserId, StatusTypeId,
    ChannelTypeId, Amount, OperationPersonId, CurrencyTypeId
)
SELECT 
    op.OperationId, op.OperationTypeId, op.BranchId, op.DateUpdated,
    op.OperationDate, op.StepCountTypeId, op.UserId, op.StatusTypeId,
    op.ChannelTypeId, cash.Amount, cash.OperationPersonId, cash.CurrencyTypeId
FROM CashDesk.Cash.Operation op
LEFT JOIN CashDesk.Cash.CashOperation AS cash WITH (NOLOCK) 
    ON op.OperationId = cash.OperationId
WHERE op.OperationDate >= '2024-04-01' 
  AND op.OperationDate < '2024-05-01' -- 替代CAST操作，利用OperationDate上的索引
  AND op.OperationTypeId IN (9, 11, 10, 13, 14, 15, 348, 369, 385, 432);

另外，给临时表启用数据压缩，进一步减少存储体积：

CREATE TABLE #Operation (...) WITH (DATA_COMPRESSION = PAGE);

4. 更新临时表统计信息 #

临时表的统计信息可能未自动更新，导致SQL Server生成低效的执行计划。手动更新统计信息：

UPDATE STATISTICS #Operation WITH FULLSCAN;

5. 创建覆盖索引（若查询频率高） #

如果需要多次查询该临时表，创建覆盖索引包含所有查询列，让SQL Server直接从索引中读取数据，避免表扫描：

CREATE NONCLUSTERED INDEX IX_Operation_Covering 
ON #Operation (OperationId) -- 选择过滤/排序常用列作为索引键
INCLUDE (
    OperationTypeId, BranchId, DateUpdated, OperationDate,
    StepCountTypeId, UserId, StatusTypeId, ChannelTypeId,
    Amount, OperationPersonId, CurrencyTypeId
);

注意：覆盖索引会增加插入时的开销，若仅查询一次，可能得不偿失。

6. 改用内存优化临时表（SQL Server 2016+） #

内存优化临时表将数据存储在内存中，完全避免磁盘I/O，适合大数量级的临时表查询：

CREATE TABLE #Operation_MemOpt (
    OperationId INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 400000), -- 哈希桶数量设为数据量的1-2倍
    OperationTypeId INT,
    BranchId INT,
    DateUpdated DATETIME2(0),
    OperationDate DATETIME2(0),
    StepCountTypeId INT,
    UserId INT,
    StatusTypeId INT,
    ChannelTypeId INT,
    Amount DECIMAL(12,2),
    OperationPersonId INT,
    CurrencyTypeId INT
) WITH (MEMORY_OPTIMIZED = ON);

-- 插入数据
INSERT INTO #Operation_MemOpt (...)
SELECT ... FROM ...;

-- 查询数据
SELECT ... FROM #Operation_MemOpt;

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