Why is "select *" faster than "select top 500 *" in SQL Server?
Adding a TOP clause to a query introduces a row goal to the query. The query optimizer will attempt to use the fact that it doesn't need to return all rows to create a more efficient query plan. The row goal can cause the cost of some operators to be scaled down. The row goal optimization can work against the favor of the query tuner due to model limitations or incomplete information in statistics objects. Below I have a demo against a simple view for which adding TOP 500 degrades performance.

First insert only odd integers into a table. Note that I'm gathering statistics in full at the end.

DROP TABLE IF EXISTS dbo.ODD;

CREATE TABLE dbo.ODD (
    ID BIGINT NOT NULL,
    FLUFF VARCHAR(10)
);

INSERT INTO dbo.ODD WITH (TABLOCK)
SELECT TOP (100000)
  -1 + 2 * ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, REPLICATE('FLUFF', 2)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

CREATE STATISTICS S ON dbo.ODD (ID) WITH FULLSCAN;


Then insert only even integers into a different table. I'm doing some things with repeated values and row size to make the demo work. I still update statistics in full at the end.

DROP TABLE IF EXISTS dbo.EVEN;

CREATE TABLE dbo.EVEN (
    ID BIGINT NOT NULL,
    FLUFF VARCHAR(3500)
);

INSERT INTO dbo.EVEN WITH (TABLOCK)
SELECT TOP (100000) 
  1000 * FLOOR ( ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) / 500)
, REPLICATE('FLUFF', 700)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2

CREATE STATISTICS S ON dbo.EVEN (ID) WITH FULLSCAN;


Here's the view definition:

CREATE OR ALTER VIEW dbo.TRICKY_VIEW AS
SELECT o.ID
FROM dbo.ODD o
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.EVEN e WHERE o.ID = e.ID
);


Consider the following query:

SELECT TOP 500 *
FROM dbo.TRICKY_VIEW
OPTION (MAXDOP 1);


Here's what the query plan looks like:



A costing limitation causes the full scan of the EVEN table on the inner side of the nested loop join to have a low relative cost. Based on how I constructed the data we know that the optimizer will need to scan 500 * 100000 = 50 million rows from the EVEN table in order to return the first 500 rows to the client. This is indeed what happens, and the query takes about 16 seconds to execute on my machine:



Removing the TOP clause from the query gives a different and more efficient plan:



This query executes in less than half a second on my machine. Only 100000 rows are read from the EVEN table.

For SQL Server 2016 and later versions you can work around this issue without changing the view's definition by adding OPTION (USE HINT('DISABLE_OPTIMIZER_ROWGOAL')) to the query. That hint disables the row goal optimization at the query level. For SQL Server 2012 you can use trace flag 4138 at the query level via OPTION (QUERYTRACEON 4138), but that requires SA.

I can't say anything about your query in particular without seeing the query plans, but hopefully this example illustrates the general point.