Optimize your query so that it does not need to sort Top N

Question

I have the following request

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

This generates the first execution plan in the picture, the second - the same request without order by


Is there a way that you can get rid of TOP N Sort (so you need only a TOP ) by indexing tables in a certain way?

Answer 1

Add the tblSystem index to systemName with fkCompanyID enabled.

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

Rewrite your query to select the first 25 values ​​(with relationships) from tblSystem in the tblSystem ordered by systemName , and then join tblCompany to get the 25 values ​​you need.

Depending on if fkCompanyID is nullable or not, you need to filter out null values ​​in the where clause in the view.

 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 still have to use the top (n) sort operator, but it will only sort the 25 rows (+ links) that you got from the tblCompany merged with tblCompany .

Answer 2

You can get rid of it by specifying a coverage index containing rows already sorted by the ASC system name, the ASC name, but my suspicion is that you are optimizing micro-optimization. Is this query slow? If this is fast, then the fact that the “top view N” takes “half the time” is not very important. If it is slow, I would be much more interested in the index, even if it is listed as 4%.