存储过程中的子查询如果使用不当，很容易成为隐藏的性能杀手。当同一个子查询出现在循环、多处JOIN条件或EXISTS判断中时，数据库引擎可能会重复扫描底层表，导致逻辑读和CPU时间成倍增长。更麻烦的是，某些优化器不会自动将子查询结果物化，而是将其内联到外层查询中，使开销进一步放大。要解决这类问题，核心思路之一就是把子查询的中间结果先缓存起来，再让后续逻辑复用。
为什么子查询会拖慢存储过程
子查询本身并不总是坏的，它的执行效率取决于是否相关子查询、是否被多次调用以及优化器能否将其转换为JOIN。相关子查询是最大的性能隐患，因为它需要为外层查询的每一行，都执行一次内层查询。例如下面的写法：
SELECT o.OrderID, o.CustomerID,
(SELECT COUNT(*)
FROM OrderDetail d
WHERE d.OrderID = o.OrderID) AS DetailCount
FROM Orders o
WHERE o.OrderDate >= '2024-01-01';
在这个例子中，内层的COUNT(*)子查询依赖于外层Orders表的每一行o.OrderID。如果Orders表返回1万行，那么内层查询就会被执行1万次，即使OrderDetail表上有合适的索引，这种逐行匹配的成本依然很高。在存储过程中，如果这样的子查询被反复调用，或者外面还套了一层循环，性能下降会更加剧烈。
非相关子查询虽然不会逐行执行，但如果同一段子查询代码在存储过程中出现多次，数据库仍然可能重复扫描相同的源表。优化器通常不会主动缓存子查询的中间结果，因为它默认每次引用都是独立的逻辑集合。这就导致相同的数据被读取、聚合了多遍，产生不必要的I/O和CPU消耗。因此，手动将子查询结果物化，是存储过程优化中非常实用的一类手段。
使用临时表缓存中间结果
临时表是SQL Server中缓存子查询结果最常用的方式之一。做法是先把子查询的结果集写入一个以#开头的本地临时表，然后在后续SQL中直接JOIN该临时表，而不是重复写子查询。这样底层表只需要被扫描一次，后续操作都在更小的中间结果集上进行。
下面是一个改写示例。原查询需要在多个地方使用订单明细的汇总数据，我们可以先把它缓存到临时表：
IF OBJECT_ID('tempdb..#OrderCache') IS NOT NULL
DROP TABLE #OrderCache;
SELECT d.OrderID, COUNT(*) AS DetailCount
INTO #OrderCache
FROM OrderDetail d
WHERE d.CreateTime >= '2024-01-01'
GROUP BY d.OrderID;
CREATE INDEX IX_OrderCache_OrderID ON #OrderCache(OrderID);
SELECT o.OrderID, o.CustomerID, c.DetailCount
FROM Orders o
LEFT JOIN #OrderCache c ON c.OrderID = o.OrderID
WHERE o.OrderDate >= '2024-01-01';
临时表的一个显著优势是可以创建索引。当中间结果集较大时，在后续JOIN或过滤条件上创建索引可以显著提升性能。此外，临时表会维护统计信息，优化器能够更准确地估算行数，从而生成更优的执行计划。临时表的生命周期与当前会话绑定，存储过程结束后可以自动释放，但仍建议在结束前显式DROP，避免在长连接场景下导致tempdb膨胀。
使用临时表也有成本。写入#temp表本身需要消耗I/O和CPU，如果中间结果很小，这个开销可能比直接执行子查询还大。因此临时表更适合数据量较大、需要多次复用的场景。如果数据量很小，表变量可能是更轻量的选择。
CTE物化与表变量的选择
很多人误以为CTE会像临时表一样缓存结果，但实际上CTE只是一个逻辑定义，默认情况下它会被内联到主查询中，多次引用同一个CTE可能导致CTE内部的查询被重复执行。例如下面的代码在没有物化提示时，DetailCTE可能被执行两次：
WITH DetailCTE AS
(
SELECT d.OrderID, COUNT(*) AS DetailCount
FROM OrderDetail d
WHERE d.CreateTime >= '2024-01-01'
GROUP BY d.OrderID
)
SELECT o.OrderID, c1.DetailCount AS JanuaryCount,
c2.DetailCount AS FebruaryCount
FROM Orders o
LEFT JOIN DetailCTE c1 ON c1.OrderID = o.OrderID AND o.OrderDate >= '2024-01-01'
LEFT JOIN DetailCTE c2 ON c2.OrderID = o.OrderID AND o.OrderDate >= '2024-02-01';
在SQL Server中，可以使用OPTION (MATERIALIZE)提示来强制CTE结果物化。这样CTE只执行一次，结果被缓存到内部工作表中。改写如下：
WITH DetailCTE AS
(
SELECT d.OrderID, COUNT(*) AS DetailCount
FROM OrderDetail d
WHERE d.CreateTime >= '2024-01-01'
GROUP BY d.OrderID
)
SELECT o.OrderID, c1.DetailCount AS JanuaryCount,
c2.DetailCount AS FebruaryCount
FROM Orders o
LEFT JOIN DetailCTE c1 ON c1.OrderID = o.OrderID AND o.OrderDate >= '2024-01-01'
LEFT JOIN DetailCTE c2 ON c2.OrderID = o.OrderID AND o.OrderDate >= '2024-02-01'
OPTION (MATERIALIZE);
需要注意的是，OPTION (MATERIALIZE)是SQL Server的专用提示，并非所有数据库都支持。在MySQL、PostgreSQL等系统中，可以通过把CTE结果插入到临时表或表变量来实现类似效果。表变量在SQL Server中也有它的适用场景：它比临时表更轻量，不涉及tempdb中的物理写入，适合缓存几十到几千行的小结果集。但表变量不维护统计信息，优化器通常假定它只有一行，这可能导致错误的连接顺序。因此当中间结果较大或需要参与复杂JOIN时，临时表是更稳妥的选择。
改写相关子查询：用JOIN和窗口函数减少逐行计算
缓存中间结果解决的是重复读取问题，但有时候根本原因是子查询本身是相关子查询。相关子查询每行执行一次，即使结果被缓存也于事无补。此时应该从查询逻辑入手，将相关子查询改写为等效的JOIN或窗口函数。
例如，要获取每个客户最近一笔订单，初学者可能会写：
SELECT o.OrderID, o.CustomerID, o.OrderDate
FROM Orders o
WHERE o.OrderDate = (
SELECT MAX(o2.OrderDate)
FROM Orders o2
WHERE o2.CustomerID = o.CustomerID
);
这个相关子查询会为Orders中的每一行执行一次MAX聚合。如果订单表有几十万行，内层查询就会被执行几十万次，性能极差。用窗口函数可以一次性完成计算：
SELECT OrderID, CustomerID, OrderDate
FROM (
SELECT o.OrderID, o.CustomerID, o.OrderDate,
ROW_NUMBER() OVER (PARTITION BY o.CustomerID ORDER BY o.OrderDate DESC) AS rn
FROM Orders o
) t
WHERE t.rn = 1;
窗口函数在扫描一次表的同时完成排序和编号，避免了逐行子查询带来的重复扫描。类似地，EXISTS子查询往往可以改写为LEFT JOIN加IS NOT NULL或INNER JOIN，标量子查询也可以拆成LEFT JOIN再接聚合。这类改写不仅减少执行次数，还能让优化器更好地选择连接顺序和索引。
存储过程计划缓存与参数嗅探的影响
在存储过程中引入临时表或物化CTE时，开发人员还需要关注执行计划的缓存行为。SQL Server会为存储过程缓存执行计划，但如果存储过程内部使用了临时表，实际执行时临时表的数据量可能随参数变化而剧烈波动。例如传入一个很大日期范围时，#OrderCache可能有几百万行；传入很小日期范围时，可能只有几十行。优化器在首次编译时基于当时参数生成的计划，未必适合后来的参数，这就是参数嗅探问题。
针对这种情况，可以在关键查询后添加OPTION (RECOMPILE)，让包含临时表JOIN的查询在每次执行时重新编译。也可以在执行完写入临时表后，手动执行UPDATE STATISTICS #temp，让优化器使用更准确的行数估算。如果存储过程内部分支较多，还可以考虑使用动态SQL拆分为多个简单语句，减少计划复用的负作用。
缓存中间结果并不是银弹，它增加了额外的写入和存储开销，因此需要在减少重复扫描与增加物化成本之间做权衡。通常建议先用执行计划和STATISTICS IO定位真正的热点，确认子查询被多次执行或产生了大量逻辑读，再决定采用临时表、表变量还是物化CTE。结合相关子查询改写，往往能把存储过程的整体开销降下来一个量级。