使用 CROSS APPLY 選擇執行緩慢

我正在嘗試優化查詢以更快地執行。查詢如下：

SELECT grp_fk_obj_id, grp_name
FROM tbl_groups as g1
        CROSS APPLY (SELECT TOP 1 grp_id as gid
                     FROM tbl_groups as g2
                     WHERE g1.grp_fk_obj_id = g2.grp_fk_obj_id
                     ORDER BY g2.date_from DESC, ISNULL(date_to, '4000-01-01') DESC) as a
WHERE g1.grp_id = gid

grp_id 是主鍵。grp_fk_obj_id 是另一個對象的外鍵。這兩列都有索引（我猜它是預設的）。

完成大約需要半秒鐘，但我需要它來加快工作速度。我查看了執行計劃，它顯示“Top N sort”的成本超過 90%。另外，我注意到如果我在交叉應用中刪除 where 子句，那麼它的執行速度至少快 5 倍，但我需要以一種或另一種方式使用 where 子句。

您是否看到任何提高此查詢性能的可能性？

編輯：表創建 DDL：

create table tbl_groups
(
   grp_id        bigint identity
       constraint PK_tbl_groups
           primary key,
   grp_fk_obj_id bigint      not null
       constraint FK_grp_fk_obj_id
           references tbl_other,
   grp_name      varchar(30) not null,
   date_from     date        not null,
   date_to       date
)
go

create index IDX_grp_fk_obj_id
   on tbl_groups (grp_fk_obj_id)
go

create index IDX_grp_name
   on tbl_groups (grp_name)
go

您需要更好的索引，以及一些重寫。

因為您只使用一個表，您可以在子查詢中使用 ROW_NUMBER()，然後從每個分區中獲取第一行。

SELECT grp_fk_obj_id, grp_name, grp_id
FROM (
   SELECT grp_fk_obj_id, grp_name, grp_id, ROW_NUMBER() OVER (PARTITION BY grp_fk_obj_id ORDER BY date_from DESC, ISNULL(date_to, '4000-01-01') DESC) AS rownum
   FROM tbl_groups
) g
WHERE g.rownum = 1;

而且，修復你的ORDER BY子句，因為它強制它進行排序。因為您正在對錶達式進行排序，所以您無法有效地索引它。如果您可以強制 date_to 有一個值 order by date_from desc, date_to desc，那麼索引 on(grp_fk_obj_id, date_from, date_to) include (grp_name)將真正有幫助。它甚至會使您的原始查詢執行得更快。

引用自：https://dba.stackexchange.com/questions/273689