Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Using in clause in query with many values

Posted on 2016-09-20
7
Medium Priority
?
61 Views
Last Modified: 2016-11-30
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
0
Comment
Question by:Rohit Bajaj
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
7 Comments
 
LVL 30

Accepted Solution

by:
Pawan Kumar earned 1000 total points
ID: 41806227
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
0
 
LVL 30

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 1000 total points
ID: 41806239
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

0
 
LVL 30

Expert Comment

by:Pawan Kumar
ID: 41806253
--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

0
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 1000 total points
ID: 41807304
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

0
 

Author Comment

by:Rohit Bajaj
ID: 41843232
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
0
 
LVL 30

Expert Comment

by:Pawan Kumar
ID: 41843237
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.
0
 
LVL 30

Expert Comment

by:Pawan Kumar
ID: 41863521
Hi Rohit,

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

Regards,

Pawan
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question