Why is "IF EXISTS" so much slower than "COUNT(*)" in this case?

Question

I am creating a report on SSRS 2008 R2. This report will only be sent when the data exists. At first, I tried to create data-driven subscription calling the stored procedure that checks if the view has data. If so, it will send the report to the defined recipients. Since the users ask to check the view every 10 mins, I set up a job to execute the job name of this data-driven subscription on SQL Server Agent. But I found out the subscription always timed out even though it only take around twenty to thirty seconds to select the view directly.

Then I tried to think other way to fulfill this requirement.

The way I tried is to create a stored procedure on SQL Server to check if the view has data first. If so, it will run the job name of the subscription created for the report on SSRS 2008 R2. In order to check the view every 10 mins, I created a job to execute this SP every 10 mins on SQL Server Angent.

The question is why it takes about 20 mins to execute SP1 with "IF EXISTS" but only 20 seconds to execute SP2 with "COUNT(*)". I'm really confused about it.
Thanks for your reply in advance. :)

SP1:

IF EXISTS (SELECT TOP 1 * FROM VIEW)
BEGIN
EXEC msdb.dbo.sp_start_job
@job_name = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxx'

END

--

IF EXISTS (SELECT TOP 1 C1 FROM VIEW ORDER BY C1)
BEGIN
EXEC msdb.dbo.sp_start_job
@job_name = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxx'

END
--

IF EXISTS (SELECT C1 FROM VIEW)
BEGIN
EXEC msdb.dbo.sp_start_job
@job_name = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxx'

END

SP2:

DECLARE @ExecRowCount INT

CREATE TABLE #Temp
(
    C1 CHAR(50), 
    C2 DECIMAL(10,0),
    C3 CHAR(50),
    C4 VARCHAR(4)
)

INSERT INTO #Temp
SELECT * FROM VIEW
SELECT @ExecRowCount = COUNT(*) FROM #Temp

DROP TABLE #Temp

IF(@ExecRowCount > 0)
BEGIN
EXEC msdb.dbo.sp_start_job
@job_name='xxxxxxxxxxxxxxxxxxxxxxxxxxxx'
END

SP3:

IF ((SELECT COUNT(*) FROM VIEW) > 0)
BEGIN
EXEC msdb.dbo.sp_start_job
@job_name = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxx'

END

Edit 2013/08/20: I added other clauses I tried in SP1 and SP3 @Click-Rex suggested.

Sorry I forgot to mention that the view is on SQL 2000. It's the root cause of this problem after I restored the related DBs of this view to SQL 2008 QAS to try. I didn't change any setting after restore.

The result is: The SP1 and SP3 takes less than 1 second and SP2 takes less than 20 seconds.

Actually, this view is quite complicated. I tried other simple views on SQL 2000. It didn't take too much time with "IF EXISTS".

Thanks for everyone's feedback. :-)

Out of interest; is `IF ((SELECT COUNT(*) FROM VIEW) > 0) BEGIN ... END` just as fast as SP2? Have you looked at the execution plan? It may be that the ordering required to get the `TOP 1 *` is expensive — Luke Merrett, Aug 19 '13 at 07:55
Try if with a unique field name rather than * - `IF EXISTS (SELECT TOP 1 [ID] FROM VIEW)` — Tanner, Aug 19 '13 at 07:59
I just tried IF ((SELECT COUNT(*) FROM VIEW) > 0) BEGIN ... END, it's as fast as SP2. As to SP1, I also tried "SELECT * FROM VIEW" and "SELECT C1 FROM VIEW"..Both of them are slow as well... — Meowmi, Aug 19 '13 at 08:06
You need to show the definition of the view and the actual execution plan for the `EXISTS` version. Because SQL Server knows it can short circuit after the first matching row the `EXISTS` probably has non blocking operators such as nested loops and an inaccurate guess as to number of executions of these. — Martin Smith, Aug 19 '13 at 08:19

score 1 · Answer 1 · answered Aug 19 '13 at 08:06

1

Perhaps the top 1 part makes SQL Server choose a particularly unlucky query plan? Try:

IF EXISTS (SELECT * FROM VIEW)

It also helps if you post a screenshot of the actual query plan for both variants.

answered Aug 19 '13 at 08:06

Andomar

232,371
49
380
404

score 0 · Answer 2 · edited May 23 '17 at 12:08

0

Try something like this:

IF EXISTS (SELECT TOP 1 * FROM VIEW ORDER BY 1)

TOP N query is known to have issues. Here are some article for you to look into

http://use-the-index-luke.com/sql/partial-results/top-n-queries

http://sqlity.net/en/908/top-n-sort-a-little-bit-of-sorting/

Why select Top clause could lead to long time cost

edited May 23 '17 at 12:08

Community

1
1

answered Aug 19 '13 at 19:52

Anup Agrawal

6,551
1
26
32

score 0 · Answer 3 · answered Aug 20 '13 at 09:31

You could also replace the

IF EXISTS

with a query that compares against NULL

So you should also try out to switch statements like

IF EXISTS (SELECT TOP 1 * FROM VIEW)

with

IF (SELECT TOP 1 * FROM VIEW) IS NOT NULL

I remember that helped me once in a while

Why is "IF EXISTS" so much slower than "COUNT(*)" in this case?

3 Answers3