交叉应用与加入性能

发布于 2025-01-26 00:11:20 字数 1075 浏览 2 评论 0 原文

我正在尝试交叉应用更好地奏效，我不得不说这尚未说服我。

我发现了一个用例，涉及每个客户的最新销售。我可以看到这肯定是光滑的，这使我每个客户能够获得多个销售：（

FROM #customers AS c CROSS APPLY (
    SELECT *
    FROM #sales AS s
    WHERE s.customerid=c.id
    ORDER BY s.date DESC OFFSET 0 ROWS FETCH FIRST 1 ROW ONLY
) AS s;

我仅出于比较目的而获得一排）。

使用CTE和多个连接，我可以使用类似的结果来获得类似的结果：

WITH CTE(customer,last_order) AS (
    SELECT customerid, max(date)
    FROM #sales
    GROUP BY customerid
)
SELECT
    *
FROM
    #sales AS s
    JOIN cte ON s.customerid=cte.customer AND s.date=cte.last_order
    JOIN customers AS c ON s.customerid=c.id
;

它看起来更混乱，每个客户只会给我一个销售。

但是，我发现后者比前者大。在我的示例数据中，交叉应用的价格为74％，而具有连接的CTE的成本为26％。有了较大的数据，我得到98％的数据，比2％。

我已经在。

我做交叉应用的方式有什么问题，还是天生更昂贵？

原文

I’m trying to work out when a CROSS APPLY works out better, and I have to say it’s yet to convince me.

I have found one use case involving the most recent sales per customer. I can see that it is certainly slick and it enables me to get more than one sale per customer:

FROM #customers AS c CROSS APPLY (
    SELECT *
    FROM #sales AS s
    WHERE s.customerid=c.id
    ORDER BY s.date DESC OFFSET 0 ROWS FETCH FIRST 1 ROW ONLY
) AS s;

(I have fetched only one row for comparison purposes).

Using a CTE and multiple joins, I can get a similar result using:

WITH CTE(customer,last_order) AS (
    SELECT customerid, max(date)
    FROM #sales
    GROUP BY customerid
)
SELECT
    *
FROM
    #sales AS s
    JOIN cte ON s.customerid=cte.customer AND s.date=cte.last_order
    JOIN customers AS c ON s.customerid=c.id
;

which looks messier, and only gives me one sale per customer.

However, I find that the latter is much lest costly than the former. In my sample data, the CROSS APPLY costs 74% while the CTE with joins costs 26%. With a larger set of data I get 98% vs 2%.

I have set up a fiddle at https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=18b7cf86195a56552bacd7e985da898c .

Is there something wrong with the way I did my CROSS APPLY, or is it inherently more expensive?