Select with CROSS APPLY runs slow

3 votes

1 answer

5450 views

sql-server execution-plan sorting cross-apply top

                          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
                        

Asked by EtherPaul (133 rep)

Aug 13, 2020, 11:16 AM
Last activity: Aug 13, 2020, 12:38 PM

Select with CROSS APPLY runs slow

Related Questions