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