Solved

replace IN with EXIST

Posted on 2013-06-18
2
266 Views
Last Modified: 2013-06-19
hi experts

Can theperformance of this sql be improved by using a EXIST clause instead of  IN ?

DELETE FROM CUSTOMER  WHERE ID IN (SELECT ID FROM CUSTOMER WHERE  System='MAC' AND BusinessDate='2013-05-06' )

thanks
0
Comment
Question by:royjayd
[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
2 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 300 total points
ID: 39258028
yes, it can be, if you are looking for a exists solution it can be written as below

DELETE FROM CUSTOMER  C
WHERE EXISTS (SELECT 1 FROM CUSTOMER C1 WHERE  C1.System='MAC' AND C1.BusinessDate='2013-05-06' and C.id = C1.ID)

Open in new window

0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 100 total points
ID: 39258112
EXISTS will generally outperform IN() - and can be substantially better.
however the difference is marginal if the numbers involved are small

my personal "rule of thumb" regarding IN() is that you don't use it if the number of items within the brackets is an unknown quantity. i.e.

select * from whatever where fieldx IN (1,2,3) -- known quantity = OK
select * from whatever where fieldx IN (select X from Y) -- unknown quantity = Not OK

IN() is an abbreviated form of a list of OR operators and can be dreadfully slow if the number of items being evaluated is very large.

ps: Neo_jarvis and I also seem to agree on using "select 1 ...".  The inner subquery only needs to return a flag when using EXISTS, and the constant 1 does this nicely.

pps: for me, "rules of thumb" are not absolute, just guides.
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

752 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