Categories
Mastering Development

Optimize a large in clause from list of integer

I would like to join on a large list of integers in SQL Server instead of a big IN clause.

My query :

SELECT
  mmr.idContact,
  mmR.idQuestion as IdQuestion,
  MIN(mmR.idResponse) AS IdResponse
FROM MatchResponses mmR
--JOIN Contact c on c.idContact = mmR.idContact //to show the linked ids
JOIN Contact c on c.idSpecific in (1,2,3...10000)
WHERE myId= 300
GROUP By mmR.idContact, mmr.idQuestion
order by idContact, idQuestion 

The IN clause is way too long, I can join mmR and COntact with an idContact.

The query takes 44s I would like to make it shorter using a JOIN

How can I declare the integers table "on the go" ?

Leave a Reply

Your email address will not be published. Required fields are marked *