SQL Server Performance Forum – Threads Archive
SQl Tuning question.
I’m currently studying Sql statements Tuning and I have a question.I have two simple queries and I would like to know which is more effective and better for performance: SELECT *
FROM SHOFET
WHERE Not Exists ( select *
from teuda
where teuda.mezahe_yeshut = SHOFET.MEZAHE_SHOFET) SELECT *
FROM SHOFET
WHERE 0 = ( select count(*)
from teuda
where teuda.mezahe_yeshut = SHOFET.MEZAHE_SHOFET) The result is the same, but what is better to use?
Thank you in advance for responces.
Try running the statement with SET STATISTICS IO ON and study the results.
I bet the first one is almost always faster than the second, because EXIST proves to a logical expression while COUNT(*) most walk through every row, even after the condition is met. Frank
http://www.insidesql.de
Thank you very much!!! and very pitty that your site is not in English……<img src=’/community/emoticons/emotion-6.gif’ alt=’
![Frown :( :(](styles/default/xenforo/smilies/frown.png)
I’m sorry for a stupid question:
How I can find statistics after I do SET STATISTICS IO ON?
First, thanks for your kind comment on my site! When running your statements in Query Analyzer type
SET STATISTICS IO ON
<your_statement>
SET STATISTICS IO OFF Hit F5 to fire your query. You then should see in the lower part of QA two tabs:
‘Grid’ and ‘Messages’. On the Messages tabs you’ll find the information. Frank
http://www.insidesql.de
Thank you again!<br />I found a solution<img src=’/community/emoticons/emotion-1.gif’ alt=’
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
also try the following: SELECT s.*
FROM SHOFET s
LEFT JOIN teuda t ON t.mezahe_yeshut = s.MEZAHE_SHOFET
WHERE t.mezahe_yeshut IS NULL it should have the same plan as the not exist, but it is usually easier to visualize the execution plan for a query with joins instead of not exists
]]>