0

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

Benoît
  • 143
  • 1
  • 2
  • 15
  • You can create custom data type (so custom data type) in SQL and you can populate that from C# when passing to the SQL SP. So you could create a list object and convert to your SQL data type for example and pass it to SQL. Then use that in your join – Brad Mar 04 '21 at 21:08
  • @Brad The list is already created in C#, I have difficulties for the format use in sql server to join on it – Benoît Mar 04 '21 at 21:13
  • Ok, then go to next step. Create custom SQL data type that is table of ints. Then in C# you populate it and send to your SQL, then just use it in the SP – Brad Mar 04 '21 at 21:18
  • 1
    See here: https://stackoverflow.com/questions/25870904/create-a-user-defined-table-type-in-c-sharp-to-use-in-sql-server-stored-procedur – Brad Mar 04 '21 at 21:19
  • @Brad There is no other solution than creating a Table ? I would like to avoid it – Benoît Mar 04 '21 at 21:20
  • 2
    Many probably but they are not the best. Your 2 best are a temp table or table type variable like I posted above. These are best for performance, readability, and ease. Any other options are going to be more complicated. – Brad Mar 04 '21 at 21:21
  • I have a few standard table types in my DB for use in various stored procedures as Table Valued Parameters. So I have single column short and long varchar/nvarchar, int, float and guid. And a few combinations of int/varchar, guid/varchar, int/int, int/float, varchar/varchar. Don't forget you can add primary keys and indexes to them. They are very fast, you should learn how to use them – Charlieface Mar 05 '21 at 00:45
  • It looks like your join condition is not actually joining tables at all so you are going to get tons duplicated rows, all of which will boil down to the same result regardless. If the intent is to join to `contact`, then add your join but also turn your `IN` into part of the `WHERE`. Ugly as it is, that may be enough. If not you can use the table variables etc. as per the other suggestions but I'd fix your query first to be something meaningful. – LoztInSpace Mar 05 '21 at 03:45

2 Answers2

1

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.

Gudwlk
  • 1,177
  • 11
  • 11
0

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
Benoît
  • 143
  • 1
  • 2
  • 15