sql - SQL Server 2005 中的 UNION ALL 性能

Question

我有一个带有一长串 CTE 的查询，它以

SELECT RegionName, AreaName, CityName, SubCityName, StreetName 
FROM tDictionaryStreets
UNION ALL
SELECT RegionName, AreaName, CityName, SubCityName, StreetName 
FROM tDictionaryRegions

此查询的执行时间为 1450 毫秒。当我分别执行这 2 个 SELECT 时，它花费的时间要少得多。对于查询

SELECT RegionName, AreaName, CityName, SubCityName, StreetName 
FROM tDictionaryStreets

执行时间为 106 毫秒。对于查询

SELECT RegionName, AreaName, CityName, SubCityName, StreetName 
FROM tDictionaryRegions

是 20 毫秒。

为什么 UNION ALL 将执行时间增加了 10 倍以上？我能做些什么来减少它？

感谢您的帮助。

更新
整个查询(我缩短了它，但问题仍然存在)是

WITH tFoundRegions AS
(
    SELECT KladrItemName FROM dbo.tBuiltKladrItemsWithQuants
    WHERE UserID = @UserID AND (indeces & 1) > 0
),
tFoundAreas AS
(
    SELECT KladrItemName FROM dbo.tBuiltKladrItemsWithQuants
    WHERE UserID = @UserID AND (indeces & 2) > 0
),
tFoundCities AS
(
    SELECT KladrItemName FROM dbo.tBuiltKladrItemsWithQuants
    WHERE UserID = @UserID AND (indeces & 4) > 0
),
tFoundSubCities AS
(
    SELECT KladrItemName FROM dbo.tBuiltKladrItemsWithQuants
    WHERE UserID = @UserID AND (indeces & 8) > 0
),
tFoundStreets AS
(
    SELECT KladrItemName FROM dbo.tBuiltKladrItemsWithQuants
    WHERE UserID = @UserID AND (indeces & 16) > 0
),
tDictionaryStreets AS
(
    SELECT DISTINCT
        CASE WHEN RegionName IN (SELECT KladrItemName FROM tFoundRegions) THEN RegionName ELSE NULL END RegionName
      , CASE WHEN AreaName IN (SELECT KladrItemName FROM tFoundAreas) THEN AreaName ELSE NULL END AreaName
      , CASE WHEN CityName IN (SELECT KladrItemName FROM tFoundCities) THEN CityName ELSE NULL END CityName
      , CASE WHEN SubCityName  IN (SELECT KladrItemName FROM tFoundSubCities) THEN SubCityName ELSE NULL END SubCityName
      , StreetName 
    FROM StreetNames
    WHERE StreetName IN (SELECT KladrItemName FROM tFoundStreets)
),
tMissingSubCities AS
(
    SELECT KladrItemName FROM tFoundSubCities
    WHERE KladrItemName NOT IN (SELECT SubCityName FROM tDictionaryStreets)
),
tDictionarySubCities AS
(
    SELECT DISTINCT 
        CASE WHEN RegionName IN (SELECT KladrItemName FROM tFoundRegions) THEN RegionName ELSE NULL END RegionName
      , CASE WHEN AreaName IN (SELECT KladrItemName FROM tFoundAreas) THEN AreaName ELSE NULL END AreaName
      , CASE WHEN CityName IN (SELECT KladrItemName FROM tFoundCities) THEN CityName ELSE NULL END CityName
      , SubCityName
      , NULL StreetName 
    FROM SubCityNames
    WHERE SubCityName IN (SELECT KladrItemName FROM tMissingSubCities)
)
SELECT RegionName, AreaName, CityName, SubCityName, StreetName 
FROM tDictionaryStreets
UNION ALL
SELECT RegionName, AreaName, CityName, SubCityName, StreetName 
FROM tDictionarySubCities

Answer 1

确保在每次测试运行之间清除执行 + 数据缓存。

例如

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

如果您先使用 UNION ALL 运行，然后再分别运行 2 个选择，则数据将已经缓存在内存中，从而使性能更好(因此给人一种错误的印象，即后续方法可能更快，但可能不是)。

如果您使用了 UNION，那么它可能会更慢，因为它必须应用 DISTINCT，但 UNION ALL 不必这样做，所以它应该没有什么不同。

更新:
查看执行计划并比较它们 - 看看是否有任何区别。在运行查询之前，您可以通过单击 SSMS 中的“包括实际执行计划”按钮来查看执行计划

更新 2:
基于给出的完整 CTE，我想我会考虑优化它们 - 我认为 UNION ALL 实际上不是问题所在。

恕我直言，最好的尝试是一个一个地处理 CTE，并尝试单独优化每个 CTE，这样当您将它们全部组合到主查询中时，它们的性能会更好。

例如对于 tDictionaryStreets，试试这个怎么样:

SELECT DISTINCT
    r.KladrItemName AS RegionName,
        a.KladrItemName AS AreaName,
        c.KladrItemName AS CityName,
        sc.KladrItemName AS SubCityName,
        s.StreetName      
FROM StreetNames s
    JOIN tFoundStreets fs ON s.StreetName = fs.KladrItemName
    LEFT JOIN tFoundRegions r ON s.RegionName = r.KladrItemName
    LEFT JOIN tFoundAreas a ON s.AreaName = a.KladrItemName
    LEFT JOIN tFoundCities c ON s.CityName = c.KladrItemName
    LEFT JOIN tFoundSubCities sc ON s.SubCityName = scc.KladrItemName

每个表上的 KladrItemName 至少应该有一个索引。
尝试以与连接相同的方式重新处理 tDictionarySubCities。