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
Solved

Using in clause in query with many values

Posted on 2016-09-20
7
53 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
  • 5
7 Comments
 
LVL 28

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 28

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 28

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 40

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 28

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 28

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
count download link and run update query 9 82
MySqlDump not dumping triggers 1 43
Sudden decrease in performance when updating mysql using classic asp 6 27
mysql db 3 69
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

840 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