Right now the query that you have looks pretty good. It seems like performance scales with the number of attributes on the entity that you're checking against. For example, if I test with the following parameters:
DECLARE @search_entity_id INT = 2896182;
DECLARE @current_count INTEGER = 8;
The query that you have with
MAXDOP 1 takes about 188 ms CPU time and 188 ms elapsed time on my desktop. If I change the parameters to these:
DECLARE @search_entity_id INT = 1324226;
DECLARE @current_count INTEGER = 4;
Then CPU time drops to 93 ms CPU and elapsed time drops to 90 ms.
One trick that can improve performance is to take advantage of batch mode execution. Batch mode execution processes rows up in batches up to 900 rows instead of row by row to cut down on metadata and access costs. It was designed to work with CCIs but can also work with data from rowstore tables. There are a few different tricks to get batch mode with rowstore queries. Here, I'll just create an empty NCCI against the temp table:
CREATE NONCLUSTERED COLUMNSTORE INDEX X_CCI ON #base (entity_id)
WHERE entity_id = -1 AND entity_id = -2;
As of SQL Server 2016, batch mode processing is supported for
MAXDOP 1 queries and for the sort operator. Both of those enhancements are helpful for this query. After creating the index we can see the difference in the query plan:
The previous 8 row example now executes in about 110 ms of CPU time and 115 ms of elapsed time. The 4 row example executes in about 62 ms of CPU time and 60 ms of elapsed time. I got a savings of around 33% of my machine, but I don't know if that's good enough for your workload's requirements.