exists or not exists

Results 1 to 2 of 2

Thread: exists or not exists

  1. #1
    Join Date
    Dec 1969

    Default exists or not exists

    In SQL, which of these has the best performance, “Not Exists", "Exists", or "IN"? I know &#039;Not Exists&#039; is very slow. So I investigated it using Google and found that a lot of people had problems with the bad performance of “Not Exists” and “Not IN”. <BR><BR>But I got different result. When I tested following 3 queries, “Not Exists” was slightly faster than others. Is it because of the amount of the data in our dev server? Or my queries are wrong? They all return the same results.<BR><BR>Please tell let me know. Thanks!<BR>

  2. #2
    Join Date
    Dec 1969

    Default Depends on what is indexed

    ...and on how many entries there are in the table.<BR><BR>Consider doing<BR><BR> WHERE fld1 NOT IN ( SELECT fld2 FROM othertable )<BR><BR>Okay, if there are 20 records in "othertable", the "SELECT fld2" is going to execute lightning fast, no matter whether fld2 is indexed or not. <BR><BR>If there are 1,000,000 records in "othertable" then "SELECT fld2" is going to be a lot slower. And it&#039;s going to be agonizingly slow if fld2 isn&#039;t indexed. The query engine actually has to go read all million records in their entirety.<BR><BR>And we haven&#039;t even BEGUN to consider the consequences of whether fld1 is indexed or not and how many records are in its table.<BR><BR>I don&#039;t see how you can possibly make any generic claims in this regard. The best you can say, I think, is "make damned sure that both fld1 and fld2 are indexed."<BR><BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts