Solved

Using in clause in query with many values

Posted on 2016-09-20
7
58 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 29

Accepted Solution

by:
Pawan Kumar earned 250 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 29

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 250 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 29

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 250 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 29

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 29

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

Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

623 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