CODEWITHSUNDEEP
sqlsql-servert-sqlquery-optimization
Jonie Shih
Jonie Shih

Reputation: 252

Query slows down in Conditional IF

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.

Upvotes: 2

Views: 154

Answers (2)

Jason W
Jason W

Reputation: 13179

How long does the code below take for you to run? If you convert your query to EXISTS, you will want to avoid the UNION since in your example you're forcing SQL to UNION every row from each of your views and then seeing if an element exists. The answer below will be opportunistic to stop as soon as possible.

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

Here's an alternate approach with TOP with a single exists - perhaps based on the provided explanations on why the multiple EXISTS were performing so slowly for you should be much better.

IF EXISTS (
    SELECT TOP (1) 1 FROM view1
    UNION ALL SELECT TOP (1) 1 FROM view2
    UNION ALL SELECT TOP (1) 1 FROM view3
    UNION ALL SELECT TOP (1) 1 FROM view4
    UNION ALL SELECT TOP (1) 1 FROM view5
    UNION ALL SELECT TOP (1) 1 FROM view6
    )
    PRINT 'Hi'

As for your question on under the hood, the url below has a good writeup: http://sqlserverplanet.com/tsql/comparing-exists-vs-left-join-where-not-null

Basically, it describes that a work table is not created when using EXISTS, so with single statements with multiple EXISTS, the performance can get slow as in your case.

Upvotes: 1

Matej Hlavaj
Matej Hlavaj

Reputation: 1038

use TOP 1 statement :

for example:

DECLARE @CONTROL INT;
WITH CTE AS (
SELECT TOP 1 col FROM view1
UNION ALL
SELECT TOP 1 col FROM view2
UNION ALL
SELECT TOP 1 col FROM view3
UNION ALL
SELECT TOP 1 col FROM view4
UNION ALL
SELECT TOP 1 col FROM view5
UNION ALL
SELECT TOP 1 col FROM view6)

SELECT @CONTROL = COUNT(col) FROM CTE

IF @CONTROL != 0
PRINT 'HI'

Upvotes: 1

Related Questions