I was working today on a slow query in SQL Server — it was a simple query on a well-indexed table, and I
could not initially see why it would be so slow. So I did some tests, and was surprised by the results.

I have reproduced the situation I was working through with a test table, with the following structure:

CREATE TABLE HintTest (
 HintTestID INT NOT NULL IDENTITY(1,1),
 Col1 INT NULL,
 Col2 INT NULL,
 Col3 INT NULL,
 CONSTRAINT PK_HintTest PRIMARY KEY (HintTestID)
);

CREATE NONCLUSTERED INDEX HintTest_Col1 ON HintTest (Col1)
-- column 2 was not indexed
CREATE NONCLUSTERED INDEX HintTest_Col3 ON HintTest (Col3)

Then I copied a set of records from the original data I was working with. The following code will
not reproduce this (and you’ll see the reason later) but does very roughly mimic the distribution of the data:

I used a quick and dirty test harness:

DECLARE @v INT, @StartTime DATETIME
SET @StartTime = GETDATE()
SET @v = 0

DECLARE @prmRecordFound BIT, @prmCol1 INT, @prmCol2 INT, @prmCol3 INT

SET @prmCol1 = 50
SET @prmCol2 = 3
SET @prmCol3 = 1750

WHILE @v < 10000
BEGIN
 SET @v = @v + 1

 -- Test Case Here
END

PRINT DATEDIFF(ms, @StartTime, GETDATE())

And here are the tests I wrote:

  -------------------------------------------
  -- TEST CASE 1: SELECT primary key and separate IF
  -------------------------------------------

  SET @prmRecordFound = 0

  DECLARE @HintTestID INT

  SELECT TOP 1 @HintTestID = HintTestID
  FROM
    HintTest
  WHERE
    Col1 = @prmCol1 AND
    Col2 = @prmCol2 AND
    Col3 = @prmCol3

  IF @HintTestID IS NOT NULL
    SET @prmRecordFound = 1

  -------------------------------------------
  -- TEST CASE 2: SELECT COUNT and separate IF
  -------------------------------------------

  SET @prmRecordFound = 0

  DECLARE @Count INT

  SELECT @Count = COUNT(*)
  FROM
    HintTest
  WHERE
    Col1 = @prmCol1 AND
    Col2 = @prmCol2 AND
    Col3 = @prmCol3

  IF @Count > 0
    SET @prmRecordFound = 1

  -------------------------------------------
  -- TEST CASE 3: SELECT COUNT nested in IF
  -------------------------------------------

  SET @prmRecordFound = 0

  IF (SELECT COUNT(*)
  FROM
    HintTest
  WHERE
    Col1 = @prmCol1 AND
    Col2 = @prmCol2 AND
    Col3 = @prmCol3) > 0
    SET @prmRecordFound = 1

  -------------------------------------------
  -- TEST CASE 4: SELECT COUNT with hint nest in IF
  -------------------------------------------

  SET @prmRecordFound = 0

  IF (SELECT COUNT(*)
  FROM
    HintTest WITH(INDEX(HintTest_Col1, HintTest_Col23))
  WHERE
    Col1 = @prmCol1 AND
    Col2 = @prmCol2 AND
    Col3 = @prmCol3) > 0
    SET @prmRecordFound = 1

  -------------------------------------------
  -- TEST CASE 5: EXISTS SELECT * in IF
  -------------------------------------------

  SET @prmRecordFound = 0

  DECLARE @Count INT

  IF EXISTS(SELECT *
  FROM
    HintTest
  WHERE
    Col1 = @prmCol1 AND
    Col2 = @prmCol2 AND
    Col3 = @prmCol3)
    SET @prmRecordFound = 1

  -------------------------------------------
  -- TEST CASE 6: EXISTS SELECT * with hint in IF
  -------------------------------------------

  SET @prmRecordFound = 0

  DECLARE @Count INT

  IF EXISTS(SELECT *
  FROM
    HintTest WITH(INDEX(HintTest_Col1, HintTest_Col23))
  WHERE
    Col1 = @prmCol1 AND
    Col2 = @prmCol2 AND
    Col3 = @prmCol3)
    SET @prmRecordFound = 1

The first run results reproduced the situation quite well, and returned the following surprising statistics (10,000 iterations):

1 (SELECT primary key and separate IF)  846 ms
2 (SELECT COUNT and separate IF)        203 ms
3 (SELECT COUNT nested in IF)           3523 ms
4 (SELECT COUNT with hint nested in IF) 226 ms
5 (EXISTS SELECT * in IF)               3460 ms
6 (EXISTS SELECT * with hint in IF)     263 ms

I was puzzled why there would be such a difference between cases 2 and 3, given that they were so similar,
so I looked at the execution plan for the query. It turns out that the query optimizer was selecting a very
non-optimal plan (a clustered index scan) when the SELECT statement was nested in an IF statement, whereas for
case 2 it was using two index seeks followed by a merge join. Adding hints overrode the query optimizer, and
the results can be seen in cases 4 and 6.

I ran the following statement to refresh the data the query optimizer uses:

UPDATE STATISTICS HintTest

UPDATE STATISTICS will update information about the distribution of keys for indexes on the table, which is then used by the query optimizer. Then I re-ran the test (100,000 iterations shown below). Dramatic difference. Now the hinted queries were among the slowest:

1 (SELECT primary key and separate IF)  2266 ms
2 (SELECT COUNT and separate IF)        2313 ms
3 (SELECT COUNT nested in IF)           2500 ms
4 (SELECT COUNT with hint nested in IF) 2546 ms
5 (EXISTS SELECT * in IF)               2656 ms
6 (EXISTS SELECT * with hint in IF)     2706 ms

For me, the big lesson learned out of this was this:

Always run UPDATE STATISTICS before trying to optimize a query!

The second thing I learned was that the fastest query is often unexpected. I would have expected the
EXISTS condition (case 5) to be optimal for a simple boolean result but instead the fastest query
was consistently case 1 – the SELECT primary key method.