Solved

replace IN with EXIST

Posted on 2013-06-18
2
257 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
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

746 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

9 Experts available now in Live!

Get 1:1 Help Now