Monday, January 10, 2011

Cost Analysis Among EXISTS (SELECT 1), EXISTS (SELECT COUNT(1)), EXISTS (SELECT *) AND EXISTS (SELECT TOP 1)



Let's understand how 'SELECT 1', 'SELECT COUNT(1)', 'SELECT *' and 'SELECT TOP 1' would behave when they are called in the WHERE EXISTS clause. Let's look at following queries and find out which one will cost least.

Query 1:
SELECT a.ProductId, a.ProductName
FROM Product.Catalog AS a
WHERE EXISTS
(SELECT *
FROM Product.Products AS b
WHERE a.ProductId = b.ProductId
AND a.ProductName = 'RAM');
GO

Query 2:
SELECT a.ProductId, a.ProductName
FROM Product.Catalog AS a
WHERE EXISTS
(SELECT 1
FROM Product.Products AS b
WHERE a.ProductId = b.ProductId
AND a.ProductName = 'RAM');
GO

Query 3:
SELECT a.ProductId, a.ProductName
FROM Product.Catalog AS a
WHERE EXISTS
(SELECT TOP 1
FROM Product.Products AS b
WHERE a.ProductId = b.ProductId
AND a.ProductName = 'RAM');
GO

Custom Search
Query 4:
SELECT a.ProductId, a.ProductName
FROM Product.Catalog AS a
WHERE EXISTS
(SELECT COUNT(1)
FROM Product.Products AS b
WHERE a.ProductId = b.ProductId
AND a.ProductName = 'RAM');
GO

There is a grave misconception among DBAs and I saw most of them choosing query 3 and 4. The clue is that work under 'WHERE EXISTS' completes as soon as the first record is returned. Query 1 and 2 are same here. Now Query 3 is more costlier than the first two and query 4 is most costly. Also query 4 is nowhere equivalent to others as this will always execute even this returns 0.

We can arrange the queries Cost wise: query 1 = query 2 < query3 < query 4


1 comment:

  1. I won't comment on the cost, seeing as you've done no tests, but query 4 is not equivalent to the others.

    Count always returns a row, regardless of how many qualifying rows there are, so the exists will always return true.

    ReplyDelete