SQL query with lots of IN parameters is slow

Question

I am executing a number of queries with many values specified in an "IN" clause, like this:

SELECT 
    [time_taken], [distance], [from_location_geocode_id],
    [to_location_geocode_id] 
FROM 
    [Travel_Matrix] 
WHERE 
    [from_location_geocode_id] IN (@param1, @param2, @param3, @param4, @param5) 
    AND [to_location_geocode_id] IN (@param1, @param2, @param3, @param4, @param5)

The example shows 5 parameters, but in practice there can be hundreds of these.

For a small numbers of parameters (up to about 400), SQL Server uses an execution plan with a number of "compute scalar" operations, which are then concatenated, sorted and joined in order to return the results.

For a large number of parameters (over 400), it uses a "hash match (right semi join)" method, which is quicker.

However, I would like it to use the second execution plan much earlier e.g. on queries with 50 parameters, since my tests have shown queries with 50-400 parameters tend to get very slow.

I've tried using various "OPTION" values on my query, but cannot get it to execute using the second execution plan, which I know would be more efficient.

I'd be grateful to anyone who can advise how to give the query the correct hints, so that it executes in the manner of the second execution plan.

Thanks

Answer 1

I think 400 parameters using the IN clause is too much. You are better off storing these values in a temporary table and doing a JOIN on it, maybe with an index on the temp table's column to speed things up.