CODEWITHSUNDEEP
c#sql-server
Benoît S
Benoît S

Reputation: 163

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" ?

Upvotes: 0

Views: 246

Answers (2)

Benoît S
Benoît S

Reputation: 163

Thanks for all, I reseigned myself to simply not use the join or anything, no filter. The filtering wasn't more efficient, I after used LinQ (I only had 200 rows) For the people looking for a solution : I could have inserted all of the id in a temp table be careful you can't insert more than 1000 rows so use this trick:

DECLARE @tempTable TABLE (id INT)

INSERT INTO @EMPLOYEEDETAILS(id)
SELECT * FROM (VALUES
  (1),(2),
  .....
   (10000)
) A(Col1)

And then the JOIN :

SELECT
   mmr.idContact,
   mmR.idQuestion as IdQuestion,
  MIN(mmR.idResponse) AS IdResponse
FROM MatchResponses mmR 
JOIN Contact c on c.idContact = mmR.idContact
JOIN @tempTable con  on con.id = c.idSpecific 
WHERE myId= 300
GROUP By mmR.idContact, mmr.idQuestion
order by idContact, idQuestion

Upvotes: 0

Gudwlk
Gudwlk

Reputation: 1157

My idea would be to handle the integer table using a temporary table in SQL. If you know the lower and upper limit of the integer table, it is easy to generate a temp_table in SQL and use it as a sub query with "In" Clause. It will not affect much on query performance.

Better to handle these from the DB rather than using an intermediate code to such as C# unless it is the requirement.

If you can attach sample schema and data, I can provide the code for you.

Upvotes: 1

Related Questions