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=’‘ />

---

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=’‘ /> Not Exist work faster than count (*).<br />and I know for the future a solution to check it!<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ /><br />

---

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

---

]]>