Is it possible to force a query to use an adaptive join? The query in question produces an adaptive join when run as a SELECT, but when run as SELECT INTO instead, it doesn't.
I have a parameterised query which has a sub-query that typically selects almost no rows (0 to 4), but occasionally picks 100k.
I believe that this is exactly the type of scenario that an adaptive join is designed for, but SQL Server doesn't pick this as a query plan.
A query plan of always-hash-join is server-destroying, so my only recourse at the moment is to force the join to be nested loops, and accept that individual worst-case requests will take a bunch of extra seconds to run.
I have an Azure SQL database at
compatability 150, and
For some reason paste-the-plan doesn't think the XML for this query is legit.
The query at issue is:
with hashes as (select h.[ContactHash] FROM HashTable h join someTable p on h.id=p.id group by [ContactHash] having count(distinct key) >1 and count(distinct p.thing) between 1 and 20), dupeContacts as (select distinct c.key,c.ContactHash from someTable c where c.[ContactHash] in (select t.[ContactHash] from hashes t ) and c.key = @id ) insert into @MyTableVar select * from dupeContacts
The two tables involved are similar to:
HashTable ( int id , ContactHash varbinary(16) ) -- millions, typically 4:1 to someTable by ID
SomeTable ( int id Primary Key , varchar(100) thing )