SQL Server Performance

IF Exists()

Discussion in 'General Developer Questions' started by CanadaDBA, Oct 20, 2004.

  1. CanadaDBA New Member

    What's the difference between the following codes?


    IF Exists( SELECT * FROM tblTemp )
    and


    IF Exists( SELECT TOP 1 * FROM tblTemp )
    Is the second one faster (optimizer)?

    CanadaDBA
  2. tdong New Member

    Yes but is that what you want ? since Top 1 return or compare the first row only if tblTemp has more than 1 row your comparation will compare all with IF Exists( SELECT * FROM tblTemp )

    -and only compare with the first row IF Exists( SELECT TOP 1 * FROM tblTemp )

    example
    tblTemp
    1
    2
    3
    4
    5
    assume looking for is 2 exists in the table your first statement return true the second return false


    By the way you post more than I do now hehhehe Senior Member. I am interested in Digital Camera and haven't stop by here for awhile.
    May the best cheaters win
  3. Twan New Member


    both are the same, I don't know why you'd ever use the second one. It seems pointless with an if exists since the if exists will return as soon as it finds the first matching row anyway

    Cheers
    Twan
  4. CanadaDBA New Member

    That's what I am looking for. If the first one returns TRUE as soon as it found only one instance of the SELECT, then there is no difference between the two IF Exists().

    So, my underestanding is that IF EXISTS( SELECT * ... ) returns true as soon as SELECT * ... found the first record. If this SELECT provides ALL the records then we can say it can take more time than the second one.

    quote:Originally posted by Twan


    both are the same, I don't know why you'd ever use the second one. It seems pointless with an if exists since the if exists will return as soon as it finds the first matching row anyway

    Cheers
    Twan


    CanadaDBA
  5. FrankKalis Moderator

    Not exactly.
    What Twan meant is, that it is completely unnecessary to specify something like TOP along with EXISTS(). EXISTS() only checks whether at least one row "exists" that meets the criteria. It stops and returns TRUE as soon as this row is found, no matter if there might any other row that also meets the criteria.


    -----------------------
    --Frank
    http://www.insidesql.de
    -----------------------
  6. CanadaDBA New Member

    This is the answer I was expecting to hear. So, EXISTS() is an inteligent function. [8D]
    Thanks guys;


    quote:Originally posted by FrankKalis

    Not exactly.
    What Twan meant is, that it is completely unnecessary to specify something like TOP along with EXISTS(). EXISTS() only checks whether at least one row "exists" that meets the criteria. It stops and returns TRUE as soon as this row is found, no matter if there might any other row that also meets the criteria.


    -----------------------
    --Frank
    http://www.insidesql.de
    -----------------------


    CanadaDBA
  7. Adriaan New Member

    Wouldn't be surprised if the TOP 1 version is slower, because of the additional processing for TOP. Depends on the actual subquery: which indexes involved, any aggregates ...
  8. CanadaDBA New Member

    [:0]
    You are kidding! I didn't know that the TOP one can be slower. Are you sure about this?


    quote:Originally posted by Adriaan

    Wouldn't be surprised if the TOP 1 version is slower, because of the additional processing for TOP. Depends on the actual subquery: which indexes involved, any aggregates ...

    CanadaDBA
  9. Adriaan New Member

    For instance if your subquery would cover 2 million rows, and you have a GROUP BY and an ORDER BY in it, and on top of that only the TOP 1 row must be returned, then a lot of processing is done before the subquery is finished.

    So it may not be a problem in your situation, but in any case the EXISTS clause will give you optimum performance.
  10. derrickleggett New Member

    <img src='/community/emoticons/emotion-1.gif' alt=':)' /> If you look at the execution plan and costs, you see that they cost the same and execute the same. The execution plan throws out the TOP statement. <br /><br /><pre><br /> IF EXISTS (SELECT TOP 1 * FROM leads_final_expanded)<br /> |--Compute Scalar(DEFINE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Expr1003]=If [Expr1005] then 1 else 0))<br /> |--Nested Loops(Left Semi Join, DEFINE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Expr1005] = [PROBE VALUE]))<br /> |--Constant Scan<br /> |--Clustered Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ops].[dbo].[leads_final_expanded].[PK_leads_final_expanded]))<br /> PRINT 1<br /> IF EXISTS (SELECT * FROM leads_final)<br /> |--Compute Scalar(DEFINE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Expr1003]=If [Expr1005] then 1 else 0))<br /> |--Nested Loops(Left Semi Join, DEFINE<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Expr1005] = [PROBE VALUE]))<br /> |--Constant Scan<br /> |--Index Scan(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[ops].[dbo].[leads_final].[idx_leads_final__credit_request]))<br /> PRINT 2<br /><br />EstimateIOEstimateCPUAvgRowSizeSubtreeCost<br />NULLNULLNULL6.4135371E-3<br />0.00.0000001116.4135371E-3<br />0.04.1799999E-696.413437E-3<br />0.01.157E-641.157E-6<br />0.003204050.003204053600.0064081<br />NULLNULLNULLNULL<br />NULLNULLNULL6.4135371E-3<br />0.00.0000001116.4135371E-3<br />0.04.1799999E-696.413437E-3<br />0.01.157E-641.157E-6<br />0.003204050.003204053600.0064081<br />NULLNULLNULLNULL<br /><br /><br /></pre><br /><br /><br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  11. FrankKalis Moderator

    AFAIK are TOP, GROUP BY or ORDER BY clauses applied on an internal working table after the basic matching rows have been found. So in conjunction with EXISTS I suspect they won't be applied at all since there is no working table to apply this clauses to.

    -----------------------
    --Frank
    http://www.insidesql.de
    -----------------------
  12. Twan New Member

    Derrick,

    you're plans are not showing the same...? one has a clustered index scan, the other a non-clustered index scan, though the statements are operating on different tables...?

    Twan
  13. derrickleggett New Member

    That's because I messed it up. I ran it again with the select statements on the same table. The execution plans were EXACTLY the same after that. Sorry...I thought I remembered to correct my post.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  14. FrankKalis Moderator

    Derrick,<br />on a sidenote. Aren't you at least some 6 hours behind in America compared to us here in Europe?<br />So, ~4:00 in the morning. Hm, quite early to get to work or quite late to go to sleep [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />-----------------------<br />--Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />-----------------------<br />
  15. derrickleggett New Member

    I just got off work at 2:10. I'm calming down a little after the IT guys decided not to show and I had to go it alone. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  16. FrankKalis Moderator

    2:10 sounds like a little bit extra work to me [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Maybe, I should rethink my wish to change to our IT department...<br /><br />-----------------------<br />--Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />-----------------------<br />
  17. Adriaan New Member

    Hm, I did a few tests and I guess SQL is smart enough to ignore TOP clauses in an EXISTS clause. Perhaps if someone with a database with a few table that have millions of rows could verify that?
  18. FrankKalis Moderator

    See my reply above [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Also,<a target="_blank" href=http://groups.google.de/groups?hl=de&lr=&q=how+select+works+celko>http://groups.google.de/groups?hl=de&lr=&q=how+select+works+celko</a><br /><br />-----------------------<br />--Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />-----------------------<br />
  19. FrankKalis Moderator

  20. derrickleggett New Member

    I would read them, if it didn't have Celko in the link name. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  21. FrankKalis Moderator

    Oh, you should read them. <br />I only chose Celko because he likes to explain how SELECT works in theory. So, before searching anywhere further, I took Celko as search criteria.<br />Celko's posts are quite funny and can offer a lot of entertainment. One might think, that he'd better learn some TSQL before posting to a MS SQL Server newsgroup, but that's just the the way it goes and I guess won't change anyway. To be honest, I like Steve Kass' replies to Celko's post more [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />-----------------------<br />--Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />-----------------------<br />
  22. derrickleggett New Member

    lol Yeah, they are kind of funny sometimes. I have read ALL of Celko's books. I just don't LIKE Celko. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  23. FrankKalis Moderator

    Can't say that, since I haven't met him personally. But from my mailing conversation with him, he seems to be a nice guy with the rest being the attitude he likes to show.


    -----------------------
    --Frank
    http://www.insidesql.de
    -----------------------

Share This Page