CROSS APPLY 性能差异 [英] CROSS APPLY performance difference

问题描述

我有一个大约需要 30 秒才能运行的 SQL 查询，该查询返回 1 条记录.CROSS APPLY 中使用的函数在使用该记录的 BrandId 运行时是即时的.

I have a SQL query that takes about 30 seconds to run that returns 1 record. The function used in the CROSS APPLY is instant when run with the BrandId of this record.

SELECT 
   b.BrandId,
   b.Name,
   ah.Type,
   c.ContactEmails,
   c.ContactNumbers,
   c.ContactLinks
FROM 
   @IdsToFilterBy ids
JOIN dbo.AccountHandler ah ON ah.AccountHandlerId = ids.Id
JOIN dbo.Brand b ON ah.RepresentedByBrandId = b.BrandId
CROSS APPLY dbo.[fn_GetBrandContactDetails](b.BrandId) AS c

但是，如果我只是更改表格，我会从 CROSS APPLY 中获得 BrandId ..

However if I just change the table I get the BrandId from for the the CROSS APPLY..

SELECT 
   b.BrandId,
   b.Name,
   ah.Type,
   c.ContactEmails,
   c.ContactNumbers,
   c.ContactLinks
FROM 
   @IdsToFilterBy ids
JOIN dbo.AccountHandler ah ON ah.AccountHandlerId = ids.Id
JOIN dbo.Brand b ON ah.RepresentedByBrandId = b.BrandId
CROSS APPLY dbo.[fn_GetBrandContactDetails](ah.RepresentedByBrandId) AS c <-- change here

查询现在只需 2 秒即可运行.当我加入 dbo.Brand b ON cah.RepresentedByBrandId = b.BrandId 时，我希望它们是相同的.

the query now only takes 2 seconds to run. As I join dbo.Brand b ON cah.RepresentedByBrandId = b.BrandId I would expect them to be the same.

有人能解释一下为什么会有巨大的性能差异吗?

Can someone explain why the huge performance difference?

更新

不同之处在于，当我使用 b.BrandId 时，CROSS APPLY 是在整个 Brand 表上运行，而当我使用 ah.RepresentedByBrandId 时，CROSS APPLY 是在整个 AccountHandler 表上运行的.AccountHandler 表要小得多.

The difference is because the CROSS APPLY is running on the whole Brand table when I use b.BrandId and the whole AccountHandler table when I use ah.RepresentedByBrandId. The AccountHandler table is considerably smaller.

但是，我希望 CROSS APPLY 仅在 JOIN 的结果上运行，这是一个 1 记录.这是可能的还是我错过了了解 CROSS APPLY?

However I was expecting the CROSS APPLY to run just on the results of the JOINs which is one 1 record. Is this possible or have I miss understood CROSS APPLY?

推荐答案

找到了.

为了强制 CROSS APPLY 在 JOIN 的结果子集上运行而不是在 JOIN 之前的整个表上运行，我使用了 OPTION (FORCE ORDER)

To force the CROSS APPLY to run on the sub set of results from the JOINs and not on the whole table before the JOINS I used OPTION (FORCE ORDER)

SELECT 
   b.BrandId,
   b.Name,
   ah.Type,
   c.ContactEmails,
   c.ContactNumbers,
   c.ContactLinks
FROM 
   @IdsToFilterBy ids
JOIN dbo.AccountHandler ah ON ah.AccountHandlerId = ids.Id
JOIN dbo.Brand b ON ah.RepresentedByBrandId = b.BrandId
CROSS APPLY dbo.[fn_GetBrandContactDetails](b.BrandId) AS c
OPTION (FORCE ORDER)

现在立即运行并查看执行计划，该函数只为一个结果而不是整个数据库表被调用.

This now runs instantly and looking at the execution plan the function is only being called for the one result and not the whole db table.

这篇关于CROSS APPLY 性能差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！