IF EXISTS question | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

IF EXISTS question

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
the SQL query optimizer already knows that you really don’t want the columns but except for this case, never use SELECT *
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
Some prefer using Select * and some prefer Select 1. But the execution plan is same Madhivanan Failing to plan is Planning to fail
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
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
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
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>
There are those who even use SELECT null
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
<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>
… 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.

<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
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>
AS madhivanan said both execution plans are same.. SURYA
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
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
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.
]]>