Hi Gail,
GilaMonster (2/20/2010)
Hunterwood (2/16/2010)
It´s allways a good rule of thumb to use TOP 1 together with EXISTS, because it prevents the database engine from doing unneccesary work.Got an example that proves that?
No. I don't. But in some situations when trying to optimize queries, I have seen better performace when changing from "exists (select * from..." to "exists (select TOP 1 1 from ...". Don't have any specific example, though.
My conclusion is also based on the article "Time Bomb Coding" by David Poole: http://www.sqlservercentral.com/articles/Performance+Tuning/69337/, wich addresses this topic (among others).
If the databases are small the performance difference might not be so huge, but it´s allways a good habbit. 🙂
A good habit is to test alternatives and see exactly what the performance difference really is, if there's one in the first place.
Your'e right. Never knows what works best in a specific case without testing alternatives.
I obviously was too fast to make my conclusion that it's allways better. Thanks for the good analyze and example!
What is your opinion on the article?
Have you ever seen a performance problem using the code bellow (copied from the article)?
IF ( SELECT COUNT(*) FROM .... WHERE ...) > 0
BEGIN
...etc
END
/Markus