Select with CROSS APPLY runs slow

Question

I am trying to optimize the query to run faster. The query is the following:

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 is a primary key. grp_fk_obj_id is a foreign key to another object. There are indexes on both of these columns (I guess it comes as default).

It takes about half a second to complete but I need it to make work faster. I took a look at the execution plan and it shows that "Top N sort" has a cost of more than 90%. Also, I have noticed that if I remove a where clause inside the cross apply then it runs at least 5x faster, but I need that where clause in one way or another.

Do you see any possibilities to improve the performance of this query?

EDIT: table creation 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

Answer 1

You need better indexing, and a bit of a rewrite.

Because you’re only using one table, you can use ROW_NUMBER() in a sub-query and then grab the top row from each partition.

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;

But also, fix your ORDER BY clause, because it forces it to do the Sort. Because you’re ordering on an expression, you can’t index it effectively. If you can force date_to to have a value, order by date_from desc, date_to desc, then an index on (grp_fk_obj_id, date_from, date_to) include (grp_name) will really help. It would even make your original query run way quicker.