Take the 2-minute tour ×

In the past we had a stored function, which looked essentially like this:


Where the SELECT would take quite a long time to execute, however, with EXISTS wrapped around we are pretty fast finished.

However we now need a "certificate" in the application, "give me one example why you returned 1". We changed the query to:

SELECT TOP 1 @cert = id FROM ...
RETURN @cert

However, this takes a much longer time now. We do not have an ORDER BY clause in our query.

Is SQL Server still retrieving ALL rows and just selecting the first afterwards? Did we oversee something? Shouldn't we have the same speed? Why does the second query take so much more time than the first one? (magnitudes of time)

share|improve this question
have you tried to use data execution plan? –  Floradu88 Mar 12 '13 at 12:22
what is the question here ... –  Mingebag Mar 12 '13 at 12:22
we are not really able to execute this function only (yes, not optimal...), and if we look at the query plan of the base procedure which is somewhere down the stack trace calling our function we only get the base procedure's plan... –  D.R. Mar 12 '13 at 12:24
@Mingebag: Look at the question marks? :-) Why do those two select statements do not have the same performance? –  D.R. Mar 12 '13 at 12:25
It may depend on expression behind these three dots (...). For EXISTS it is usually enough (for SQL server) to check some related table or index records count, no actual data reads are required. To get any field value, SQL needs to read actual data, which does take more time. Can you look for @cert (ID) value in some other part of your query? Is ID field indexed? –  Arvo Mar 12 '13 at 12:34
show 2 more comments

1 Answer

Your if exists checks the existence of a matching condition and stops after it finds one, but never has to return the result. I believe that as soon as you are selecting from the set, it has to return something and depending on the set it could take awhile for the top evaluation?

If it is taking a long time, I would investigate the execution plan and see if any indexes are available to you to add.

share|improve this answer
I would give the same answer totally agree !! –  Mingebag Mar 12 '13 at 13:20
add comment

Your Answer


