Using in clause in query with many values

Rohit Bajaj
Rohit Bajaj used Ask the Experts™
on
Hi,
In my table i have several columns with the id as primary key.
I am using a query like select * from note where conversation_id in ('id1','id2',....'id300');
This is an extreme case where i will be querying for around 300 ids..
And this ran in 15 ms.
I tried setting an index on conversation_id but the time taken by it is still the same.

How can i improve the performance of this in clause.

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Expert
Awarded 2016
Top Expert 2016
Commented:
INSERT these ids in a temp table lets say #temp

SELECT  * FROM note n
INNER JOIN #Temp t on  t.conversation_id  = n.conversation_id
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016
Commented:
Even better,..

--
SELECT  n.* FROM note n
CROSS APPLY
(
   SELECT TOP 1 1 FROM  #Temp t on  t.conversation_id  = n.conversation_id
)r

--

Open in new window

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
--SPLIT Technique

DECLARE @x AS XML=''

DECLARE @Param AS VARCHAR(1000) = 'Ind,Hary,Gu'

SET @x = CAST('<A>'+ REPLACE(@Param,',','</A><A>')+ '</A>' AS XML)

;WITH CTE AS
(
	SELECT t.value('.', 'VARCHAR(10)') Value FROM @x.nodes('/A') AS x(t)
)
SELECT  n.* FROM note n
CROSS APPLY
(
    SELECT TOP 1 Value FROM
	CTE r
	WHERE r.Value  = n.conversation_id
)y

--

Open in new window

SharathData Engineer
Commented:
Pawan's approach with temp table is good idea to improve the performance.
MySQL has different syntax for creating temp table.

CREATE TEMPORARY TABLE temp_ids (conversation_id int);
INSERT INTO temp_ids VALUES ('id1'),('id2'),('id3'); -- insert all your ids here

-- Then you can join this temporary table to your actual table
SELECT n.* FROM note n
INNER JOIN temp_ids t on t.conversation_id  = n.conversation_id

Open in new window

Author

Commented:
HI,
In the temp table approach are you also counting the time to insert all the values in the temporary table... ?
So what i understand is that
1) insert all ids into temp table and then query using join
will be faster than
2) select * from .... where id in (....)

Can you also ping some article or link to understand this behavior why is this so ?
Also is the performance difference significant ?
Thanks
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
So basically what we are doing it we are first creating a temp table and insert all the values in there, This will be very fast because we are not disturbing the Main database.
Then we are joining this temp table with the user table [note in your case]. Once you join you will get records which are common in both the tables, note and the temp table.
If we directly do In with the Main table then it will be slow.


Have you tried this.. [A sample by Sharath.]

CREATE TEMPORARY TABLE temp_ids (conversation_id int);
INSERT INTO temp_ids VALUES ('id1'),('id2'),('id3'); -- insert all your ids here

-- Then you can join this temporary table to your actual table
SELECT * FROM note n
INNER  JOIN temp_ids t on t.conversation_id  = n.conversation_id

--Put a where clause whatever row is not required. OR Paste your complete query , will try to modify it.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Hi Rohit,

Do you need more help with this question? A feedback will be appreciated. :)

Regards,

Pawan

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial