Solved

Using in clause in query with many values

Posted on 2016-09-20
7
44 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 23

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 23

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 23

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 23

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 23

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

932 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now