You didn't actually provide a question to answer, so I will assume that you're looking for a way to improve the performance of your query. People have written books on that topic so it's difficult to give an answer that fits in the site's format when an execution plan cannot be provided. With that said, the strategy that I use most often is to figure out why a query is slow. Comparing the actual plans between a fast and slow version of similar queries can be very helpful. For a query like yours, the issue might boil down to a missing index or a cardinality estimate issue.
If you want an approach that's nearly guaranteed to work, trying using a temp table. You can insert the unfiltered 2k rows into a temp table in a few seconds and it'll take less than a second to apply the filtering to the temp table with a second
SELECT query. Using a temp table here avoids diagnosing the root cause of the issue, but there's nothing wrong with using a temp table to aid the performance of a complex query, or really any query.
In the comments, you asked the following question:
[C]an I instruct SQL to just apply the addition filter on the 2000
rows selected by the initial query?
This cannot be done directly but you can achieve the desired behavior with careful placement of
TOP. Adding a
TOP N with an extremely large
N won't change the results of the query but it can change optimizer behavior. The optimizer must guarantee correctness in whatever plan it creates. You and I may know that taking the first 987,654,321,987,654,321 rows will never change the results but the optimizer will implement and honor that check because it cannot guarantee that it wouldn't change the results. For a query like the following:
SELECT q.Id, q.Decimals
SELECT TOP (987654321987654321) target.Id, target.Decimals, target.count
JOIN dbo.t2 on ...
JOIN dbo.t3 on ...
JOIN dbo.t4 on ...
JOIN dbo.target on ...
WHERE t1.Id = ...
AND t2.CreatedAt >= ''
AND t2.Type = ''
AND target.Id NOT IN (
WHERE s1.Type = '' AND s2.Type = ''
WHERE q.count = 0;
The optimizer will not give you a plan that pushes down the
count filter into the derived table. That means that it's pretty likely that you'll get the same query plan that finishes in a few seconds for the derived table, and overall performance should be roughly on par with that query. I expect that the slow query plan that runs for longer than a minute would be avoided.