外部选择中操作的 CTE 查询速度急剧下降

Question

我有一个复杂的报告（类似于下面的示例），它在 52 秒内只返回 42 个项目（慢但做复杂的连接）当我执行以下操作时它会大大减慢速度：

1. 在外部选择中添加一列以获取结果中的每个项目（增加 150 秒）- 请参阅函数。
2. 将结果插入变量表（增加 120 秒），我希望将其扁平化会减少 （1） 问题，但没有雪茄。

我如何理解执行是首先执行 where 然后执行选择中的逻辑（应该只对 52 个结果项执行）。但是，如果我在 （52） 场景中运行精确的 1 个项目，则在外部 CTE 选择上使用它时，只需要 7 秒，而添加的 150 个项目

。

为什么会这样，如何在没有臃肿的执行时间的情况下添加列？

CREATE FUNCTION PriorShippers
(
    @customerId nchar(5)
)
RETURNS varchar(500)
AS
BEGIN
    DECLARE @return varchar(500);
    with data as
    (
        select distinct S.CompanyName from Customers C
          join Orders O on C.CustomerID = O.CustomerID
          join Shippers S on O.ShipVia = S.ShipperID
          where C.CustomerID = @customerId
    ) select @return = STUFF((select CompanyName + ' ' from data FOR XML PATH('')),1,0,'')
    return @return
END

查询（我使用了罗斯文数据库 - 此处的 SQL 2012 安装说明）

DECLARE @categories TABLE
(
    Name varchar(100),
    SourceCountry varchar(100)
);
insert into @categories VALUES ('Seafood', 'US');
insert into @categories VALUES ('Beverages', 'US');
insert into @categories VALUES ('Condiments', 'US');
insert into @categories VALUES ('Dairy Products', 'India');
insert into @categories VALUES ('Grains/Cereals', 'India');
with data as
(
    select C.CustomerID, C.CompanyName,
        (CASE WHEN EXISTS(select * from Orders O where O.CustomerID = C.CustomerID) THEN
            (select count(distinct CAT.CategoryID) from Orders O 
                join [Order Details] OD on O.OrderID = OD.OrderID
                join Products P on OD.ProductID = P.ProductID
                join Categories CAT on P.CategoryID = CAT.CategoryID
             where EXISTS(select * from @categories where Name = CAT.CategoryName AND SourceCountry = 'US'))
        ELSE 0 END) as 'US Orders', 
        (CASE WHEN EXISTS(select * from Orders O where O.CustomerID = C.CustomerID) THEN
            (select count(distinct CAT.CategoryID) from Orders O 
                join [Order Details] OD on O.OrderID = OD.OrderID
                join Products P on OD.ProductID = P.ProductID
                join Categories CAT on P.CategoryID = CAT.CategoryID
             where EXISTS(select * from @categories where Name = CAT.CategoryName AND SourceCountry = 'India'))
        ELSE 0 END) as 'India Orders'
    from Customers C
) select top 10 CompanyName, [US Orders], [India Orders]
    -- Below: Adding this have significant slow down
    , dbo.PriorShippers(CustomerID) 
    from data where [US Orders] > 0 Order By [US Orders]

Answer 1

您正在为每个选定的行执行用户定义的函数。简而言之，不要那样做。

将函数中的代码直接移动到报表中的 DTE 中。

另外，我注意到您的计数的连接是相同的。它们看起来像是第三个 DTE 的主要候选项，您只需加入这些 DTE 即可进一步提高整体查询的性能。