I have a Query that is dynamically created beacause we need to check against a list of int's in the where clause.
SELECT a, b From table where a in(1,5,98,34,2)
This creates a lot of single use plans. I've looked at a table value functions which takes a csv and creates a table. This Query looks like this:
SELECT a, b From table where a in(SELECT Word FROM dbo.fn_splitStrings('1,5,98,34,2',','))
This only creates one query plan but it is about 10 times slower according to SET STATISTICS TIME ON.
Any one got an idea on how to improve this?