sql - Why select Top clause could lead to long time cost -

the following query takes forever finish. if remove top 10 clause, finishs rather quickly. big_table_1 , big_table_2 2 tables 10^5 records.

i used believe top clause reduce time cost, it's apparently not here. why???

select top 10 servicerequestid  (     (select *        big_table_1      big_table_1.statusid=2     ) cap1     inner join       big_table_2 cap2     on cap1.servicerequestid = cap2.customerreferencenumber     ) 

there other stackoverflow discussions on same topic (links @ bottom). noted in comments above might have indexes , optimizer getting confused , using wrong one.

my first thought doing select top serviceid (select *....) , optimizer may have difficulty pushing query down inner queries , making using of index.

consider rewriting as

select top 10 servicerequestid    big_table_1 inner join big_table_2 cap2 on cap1.servicerequestid = cap2.customerreferencenumber , big_table_1.statusid = 2 

in query, database trying merge results , return them , limit top 10 in outer query. in above query database have gather first 10 results results being merged, saving loads of time. , if servicerequestid indexed, sure use it. in example, query looking servicerequestid column in result set has been returned in virtual, unindexed format.

hope makes sense. while hypothetically optimizer supposed take whatever format put sql in , figure out best way return values every time, truth way put our sql can impact order in steps done on db.

select top slow, regardless of order by

why doing top(1) on indexed column in sql server slow?