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

create index IDX_grp_fk_obj_id
    on tbl_groups (grp_fk_obj_id)

create index IDX_grp_name
    on tbl_groups (grp_name)
  • 2
    Yes, you may be able to use a CTE and windowing functions instead of CROSS APPLY to avoid cursor-like performance. Add CREATE TABLE DDL, and sample data INSERT statements to your question to facilitate a working answer. – Dan Guzman Aug 13 at 11:54

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
    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.

| improve this answer | |
  • Thank you, sir. This improved the query speed 7x. Also, I have changed order by to this: IIF(date_to is null, '4000-01-01', date_to) DESC and added index that you suggested. – EtherPaul Aug 14 at 16:34
  • 1
    Any function on date_to will cause the Sort operator to be there. Try running with just ORDER BY date_from DESC and see if it’s quicker still. But also check that the results are what you need. If you can force the date_to column to never be null, you can ORDER BY date_from DESC, date_to DESC which will be good too and give you the results you want. – Rob Farley Aug 15 at 4:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.