Sql - adding top 100 percent speeds up query many times

[413x]

Never seen this before.

I have a query, where I am join it back onto itself with a different subset each time using a where

So

Select
*
From
(select * from t
Where x
Inner join
(select * from t
Where y
)
On conditions

Etc

4 join. Conditions present

Adding top 100 pc to each sub query speeds the query up many times over.
It makes no sense to me.

Any ideas?

 

[samcat]

How many rows in the table t?

Assuming T has > 100 rows - Limiting rows on the subset massively reduces the data that needs to be held in memory/temp space and also reduces the complexity of the resulting looking. Being nested multiple times

Why do you need 3 layer nested subqueries to achieve this? There is almost certainly a better way! Share some more context, schema/table design etc and can probably guide on this.

Sam
(SQL developer and DBA for a long time)