Optimize query so it does not need a Top N sort

Question

I have the following query

select top 25
    tblSystem.systemName,
    tblCompany.name
from 
    tblSystem
    join tblCompany
        on tblSystem.fkCompanyID = tblCompany.pkCompanyID
order by
    tblSystem.systemName, 
    tblCompany.name

That generates the first execution plan in the picture, the 2nd is the same query without the order by


Is there any way a can get rid of the TOP N Sort (so only a TOP is needed) by indexing the tables in a particular way?

Answer 1

Add an index to tblSystem on systemName with fkCompanyID included.

create index IX_tblSystem_systemName 
  on tblSystem(systemName) include(fkCompanyID)

Rewrite your query to pick the 25 first values (with ties) from tblSystem in a derived table ordered by systemName and then join to tblCompany to get the 25 values you need.

Depending on if fkCompanyID allows null values or not you need to filter out null values in the where clause in the derived table.

select top (25)
  S.systemName,
  C.name 
from (
     select top (25) with ties
       S.fkCompanyID,
       S.systemName
     from tblSystem as S
     where S.fkCompanyID is not null
     order by S.systemName
     ) as S
  inner join tblCompany as C
    on S.fkCompanyID = C.pkCompanyID
order by S.systemName,
         C.name

You will still have to top(n) sort operator but it will only sort the 25 rows (+ ties) that you got from the derived table joined against tblCompany.