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 *
WHERE Not Exists ( select *
where teuda.mezahe_yeshut = SHOFET.MEZAHE_SHOFET) SELECT *
WHERE 0 = ( select count(*)
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
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
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
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