Skip to content

Instantly share code, notes, and snippets.

@lukaseder
Created September 18, 2016 17:15
Show Gist options
  • Save lukaseder/2929fee7da9cc0cd50b66b8708abfc0e to your computer and use it in GitHub Desktop.
Save lukaseder/2929fee7da9cc0cd50b66b8708abfc0e to your computer and use it in GitHub Desktop.
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';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment