Using Microsoft SQL Server, I had a query that retrieved rows from a view of some complexity, i.e. something like this:
SELECT * FROM vwComplex WHERE ...
This query ran basically instantaneously, even though the view involved numerous UNION ALLs and joins on coalesced keys. My sample query criteria selected ~100 out of the view's ~80,000 rows.
In the general case, I needed a query that returned a single bit indicating whether the specific criteria will select at least one row. I tried this:
SELECT CASE WHEN EXISTS ( SELECT * FROM vwComplex WHERE ... ) THEN 1 ELSE 0 END
I presumed that this statement would run faster than the original unadorned query since it can stop executing as soon as it finds the first row. My presumption was incorrect -- the query ran for a long, long time.
In this case, at least, it turns out that it is faster to run the full query than to perform an existence check:
SELECT SIGN(COUNT(*)) FROM vwComplex WHERE ...
No surprises here, of course. SQL query plan generation remains a mystical exercise.