1. 首页
  2. 问题
  3. SQL Server 2005中的UNION ALL性能
一尘不染

SQL Server 2005中的UNION ALL性能

sql

我有一个长链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倍以上？我该怎么做才能减少呢？

谢谢您的帮助。

UPDATED 整个查询（我将其缩短，但问题仍然存在）是

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

阅读 138

收藏
2021-05-23

共1个答案

一尘不染

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

例如

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

如果先使用UNION ALL运行，然后再分别运行2
selects，则数据将已经缓存在内存中，从而使性能大大提高（因此，错误的印象是，后一种方法可能会更快）。

如果您使用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。

2021-05-23