SQL Server EXISTS vs COUNT(*).sql

SQL Server EXISTS vs COUNT(*).sql

USE sakila;

DECLARE @ts DATETIME;
DECLARE @repeat INT = 10000;
DECLARE @i INT;
DECLARE @dummy VARCHAR;

DECLARE @s1 CURSOR;
DECLARE @s2 CURSOR;

SET @s1 = CURSOR FOR 
-- Paste statement 1 here
SELECT CASE WHEN EXISTS (
    SELECT * FROM actor a
    JOIN film_actor fa ON a.actor_id = fa.actor_id
    WHERE a.last_name = 'WAHLBERG'
) THEN 1 ELSE 0 END;

SET @s2 = CURSOR FOR 
-- Paste statement 2 here
SELECT count(*)
FROM actor a
JOIN film_actor fa ON a.actor_id = fa.actor_id
WHERE a.last_name = 'WAHLBERG';

SET @ts = current_timestamp;
SET @i = 0;
WHILE @i < @repeat
BEGIN
  SET @i = @i + 1

  OPEN @s1;
  FETCH NEXT FROM @s1 INTO @dummy;
  WHILE @@FETCH_STATUS = 0
  BEGIN
    FETCH NEXT FROM @s1 INTO @dummy;
  END;

  CLOSE @s1;
END;

DEALLOCATE @s1;
PRINT 'Statement 1: ' + CAST(DATEDIFF(ms, @ts, current_timestamp) AS VARCHAR) + 'ms';

SET @ts = current_timestamp;
SET @i = 0;
WHILE @i < @repeat
BEGIN
  SET @i = @i + 1

  OPEN @s2;
  FETCH NEXT FROM @s2 INTO @dummy;
  WHILE @@FETCH_STATUS = 0
  BEGIN
    FETCH NEXT FROM @s2 INTO @dummy;
  END;

  CLOSE @s2;
END;

DEALLOCATE @s2;
PRINT 'Statement 2: ' + CAST(DATEDIFF(ms, @ts, current_timestamp) AS VARCHAR) + 'ms';