sql server - SQL IN clause causes scan on too many parameters -

i'm having trouble understanding result of query here: http://pastebin.com/sgvnq1ju.

it works intended, whenever t2.cityid in (...) clause of clusters cte expression reaches more 6 values match, execution plan changes including expensive table scan, , query gets slow.

any ideas on why might be?

try avoiding in clause in favor of join on cte table constructed parameter values in clause (below)

additionally, make sure have index on map.clusterdist.cityid column.

declare @cityids0 int, @cityids1 int, @cityids2 int, @cityids3 int, @cityids4 int, @cityids5 int, @cityids6 int, @cityids7 int  select @cityids0 = 0, @cityids1 = 1, @cityids2 = 2, @cityids3 = 3, @cityids4 = 4, @cityids5 = 5, @cityids6 = 6, @cityids7 = 7  ;with cities (               select @cityids0 [cityid]     union select @cityids1     union select @cityids2     union select @cityids3     union select @cityids4     union select @cityids5     union select @cityids6     union select @cityids7 ), clusters (     select t1.*           ,t2.cityid       map.cluster t1       join map.clusterdist t2          on t2.id    = t1.distid       join cities c          on c.cityid = t2.cityid        , t2.zoom  = @zoom ) -- etc... select * clusters 

if doesn't work performance needs, may time switch out of cte tables old temp tables can index particular scenario.