I noticed that the following took 32 seconds to run in SQL Server (2008 R2)

```
IF  ((SELECT COUNT(*) FROM view1) != 0)
    OR ((SELECT COUNT(*) FROM view2) != 0)
    OR ((SELECT COUNT(*) FROM view3) != 0)
    OR ((SELECT COUNT(*) FROM view4) != 0)
    OR ((SELECT COUNT(*) FROM view5) != 0)
    OR ((SELECT COUNT(*) FROM view6) != 0)
        PRINT 'HI'

```

Yet, the following only took 4 seconds

```
SELECT 
    (SELECT COUNT(*) FROM view1)
    , (SELECT COUNT(*) FROM view2)
    , (SELECT COUNT(*) FROM view3)
    , (SELECT COUNT(*) FROM view4)
    , (SELECT COUNT(*) FROM view5)
    , (SELECT COUNT(*) FROM viwe6)

```

I have managed to optimize the conditional by doing SELECT COUNT(1)... + SELECT... != 0 such that it takes 4 seconds but looking at the execution plans haven't yielded anything particularly notable. Haven't had much chance with googling the word OR either.

Could someone please share what SQL Server optimizer is potentially doing behind the back?

EDIT: IF EXISTS attempt took 38seconds.

```
IF EXISTS ((SELECT 1 FROM view1)
    UNION (SELECT 1 FROM view2)
    UNION (SELECT 1 FROM view3)
    UNION (SELECT 1 FROM view4)
    UNION (SELECT 1 FROM view5)
    UNION (SELECT 1 FROM view6))
        PRINT 'HI'

```

EDIT2: The current 5 second method.

```
IF  (SELECT COUNT(1) FROM view1)
  + (SELECT COUNT(1) FROM view2)
  + (SELECT COUNT(1) FROM view3)
  + (SELECT COUNT(1) FROM view4)
  + (SELECT COUNT(1) FROM view5)
  + (SELECT COUNT(1) FROM viwe6) != 0
        PRINT 'HI'

```

EDIT3: After comparing the 20page sized query plans - it appears the speedup is mostly due to underlying views are doing a partial aggregate before their joins as opposed to doing joins then aggregates.