SQL Server Performance

IF EXISTS question

Discussion in 'T-SQL Performance Tuning for Developers' started by vernonreeve, Jun 19, 2006.

  1. vernonreeve New Member

    Is it better to use
    1) IF EXISTS (select * from sometable where keyfield = @keyfield)
    or
    2) IF EXISTS (select keyfield from sometable where keyfield = @keyfield)

    It seems like option 2 would be faster because it doesn't return the data for all the other fields, but I always see option 1.

    Any thoughts?
    Vern
  2. joechang New Member

    the SQL query optimizer already knows that you really don't want the columns

    but except for this case, never use SELECT *
  3. FrankKalis Moderator

    EXISTS only return TRUE or FALSE. SQL Server immediately stops execution after the first matching row is found and returns TRUE. No rows are returned. Therefore it's almost all the same, whether you use SELECT *, SELECT column, or SELECT 1.

    If you're ultimately after the best performing solution, you should use SELECT <constant> instead of SELECT *. The reason is that the SQL Server parser expands the * into the actual column list, whenever the * is encountered. You'll maybe save some computational power when using the constant expression, though the time difference will be hardly measurable.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
  4. Madhivanan Moderator

    Some prefer using Select * and some prefer Select 1. But the execution plan is same

    Madhivanan

    Failing to plan is Planning to fail
  5. Madhivanan Moderator

    Frank, I didnt see your reply when I posted. When the execution plan is same how is select 1 effecient? Does it matter when queried against a table that has millions of rows?

    Madhivanan

    Failing to plan is Planning to fail
  6. FrankKalis Moderator

    No. The only difference is that when SQL Server compiles the execution plan it always expands the * into the column list. So, it must get the column list from somewhere in memory or from disk. That action requires some more computational power than computing a constant. But the actual difference in time should be really, really small. And once the execution plan is compiled there isn't a difference anyway.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
  7. Madhivanan Moderator

    Thanks. I tested with

    If exists(Select * ...)
    If exists(Select top 1 * ...)
    If exists(Select 1 ...)
    If exists(Select top 1 ...)

    All show the same execution plan

    Madhivanan

    Failing to plan is Planning to fail
  8. FrankKalis Moderator

    Sure. The "difference" happens when that plan is compiled. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>
  9. Adriaan New Member

    There are those who even use SELECT null ...
  10. Madhivanan Moderator

    quote:Originally posted by Adriaan

    There are those who even use SELECT null ...
    Yes. You can have any constant value

    Select 'Whatever_You_want'

    But execution plan is same

    Madhivanan

    Failing to plan is Planning to fail
  11. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />Yes. You can have any constant value<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />How can NULL be a constant, when the value is unknown and one NULL marker cannot be compared to another NULL marker? [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>
  12. Adriaan New Member

    ... but to return to the original question ...

    When using EXISTS or NOT EXISTS, note that when this applies to only a relatively small number of rows, and the columns are not properly covered by an index, performance might be slower than expected.

    EXISTS is not always faster than doing a COUNT(*) or something similar. Use it mainly when the likelihood of finding a match is high, which is where a COUNT(*) would probably take more time.

    NOT EXISTS is kind of special - I wouldn't recommend using it unless you're searching on columns with an appropriate index.
  13. Madhivanan Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />Yes. You can have any constant value<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />How can NULL be a constant, when the value is unknown and one NULL marker cannot be compared to another NULL marker? [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><pre id="code"><font face="courier" size="2" id="code"><br />If NULL is NULL <br />print 'Yes, NULL is NULL'<br />else<br />print 'No, NULL is not NULL'<br /></font id="code"></pre id="code"><br />[<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  14. FrankKalis Moderator

    Not bad! But have you ever wondered why you need to compare with ... <font color="blue">IS</font id="blue"> NULL, but use SET ... <font color="blue">=</font id="blue"> NULL in an UPDATE statement? [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>
  15. vsnreddi New Member

    AS madhivanan said both execution plans are same..

    SURYA

  16. vsnreddi New Member

    quote:Originally posted by vernonreeve

    Is it better to use
    1) IF EXISTS (select * from sometable where keyfield = @keyfield)
    or
    2) IF EXISTS (select keyfield from sometable where keyfield = @keyfield)

    It seems like option 2 would be faster because it doesn't return the data for all the other fields, but I always see option 1.

    Any thoughts?
    Vern
    ------------------------------------------------------------

    1) IF EXISTS (select * from sometable where keyfield = @keyfield)
    or
    2) IF EXISTS (select 1 from sometable where keyfield = @keyfield)

    If you use select 1 from table; it wont hit the database table columns.only it search will happen and returns if sucess 1 else nothing.you can expect faster execution compare to first one.


    SURYA

  17. FrankKalis Moderator

    quote:
    If you use select 1 from table; it wont hit the database table columns.only it search will happen and returns if sucess 1 else nothing.you can expect faster execution compare to first one.
    Have you actually read this thread? The only difference lies in the compilation of the execution plans. Thereafter all methods behave absolutely identical.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
  18. balaganesh2k Member

    Instead of using IN and NOT IN better to use EXISTS AND NOT EXISTS.

    EXISTS and NON EXISTS give us better performance than IN and NOT IN

    Ganesan B.
    Well defined problem is half way solved.

Share This Page