I am trying to short circuit an OR condition with an EXISTS subquery.

Given the following rather simple query:

SELECT 
    COUNT(*)
FROM 
    firmen_spezifisch f
WHERE 
    f.dok_dat_feld_86 = '671827194002' 
    AND f.dok_dat_feld_13 <> 'Ja' 
    AND (f.dok_dat_feld_2 IN ('GRP_A', 'GRP_B', 'GRP_C', 'GRP_D', 'GRP_E', 'GRP_F', 'GRP_G', 'GRP_H', 'GRP_I')
         OR EXISTS (SELECT 1 
                    FROM benutzer_rollen br 
                    JOIN doc_roll_header rh ON br.roll_id = rh.roll_id 
                    WHERE br.benutzername = 'Usr_X' AND rh.roll_text = 'Admin'))

The query should evaluate dok_dat_feld_2 only if the EXISTS subquery does not succeed. In my brain, the EXISTS subquery should only be executed once and if successful short circuit the OR condition, so that dok_dat_feld_2 does not have to be evaluated at all.

But this does not work. The EXISTS subquery is not executed once, but every single time. The subquery does only contain 'static' filter criteria, so the result could not change during the duration of the outer query.

Can someone help me to fix the issue respectively optimize the query?

PS: I am running SQL Server 2022 Enterprise

Edit: current execution plan: /?id=FeTZ3hPwZy

I am trying to short circuit an OR condition with an EXISTS subquery.

Given the following rather simple query:

SELECT 
    COUNT(*)
FROM 
    firmen_spezifisch f
WHERE 
    f.dok_dat_feld_86 = '671827194002' 
    AND f.dok_dat_feld_13 <> 'Ja' 
    AND (f.dok_dat_feld_2 IN ('GRP_A', 'GRP_B', 'GRP_C', 'GRP_D', 'GRP_E', 'GRP_F', 'GRP_G', 'GRP_H', 'GRP_I')
         OR EXISTS (SELECT 1 
                    FROM benutzer_rollen br 
                    JOIN doc_roll_header rh ON br.roll_id = rh.roll_id 
                    WHERE br.benutzername = 'Usr_X' AND rh.roll_text = 'Admin'))

The query should evaluate dok_dat_feld_2 only if the EXISTS subquery does not succeed. In my brain, the EXISTS subquery should only be executed once and if successful short circuit the OR condition, so that dok_dat_feld_2 does not have to be evaluated at all.

But this does not work. The EXISTS subquery is not executed once, but every single time. The subquery does only contain 'static' filter criteria, so the result could not change during the duration of the outer query.

Can someone help me to fix the issue respectively optimize the query?

PS: I am running SQL Server 2022 Enterprise

Edit: current execution plan: https://www.brentozar/pastetheplan/?id=FeTZ3hPwZy

• sql
• sql-server
• query-optimization

Share Improve this question Follow edited Mar 20 at 17:39 marc_s 756k184184 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Mar 20 at 15:26 Marcel KocksMarcel Kocks 4955 bronze badges 4

• 2 Please share the query plan via brentozar/pastetheplan and please show the table definitions with indexes. Do you have a specific performance problem here? The optimizer normally works out the best way to plan the query. Specifying table aliases on each reference to a column would help us: which table is roll_text part of, for example? – Charlieface Commented Mar 20 at 15:28
• 2 SQL standards don't enforce any specific way to run queries. The whole point of SQL is that it's a declarative language: you say what you want, not how you want it. If SQL Server optimizer decides that running the subquery is less expensive, it'll do so. (Also, short-circuit evaluation in languages that implement it is typically left to right anyway). – Álvaro González Commented Mar 20 at 15:30
• My main problem is, that I do not understand why the subquery is ran multiple times and not only once. The query WHERE br.benutzername = 'Usr_X' AND rh.roll_text = 'Admin' is static and cannot change from row to row. So also the result cannot change and the subquery should only be executed once. – Marcel Kocks Commented Mar 20 at 15:46
• If I split the query in two and use an UNION instead of OR, the query returns almost instant and the plan shows only one execution of the EXISTS subquery: – Marcel Kocks Commented Mar 20 at 15:57

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

If the EXISTS subquery is non-correlated then you can move it into an IF statement

Depending on the design of the data, you may be able to convert the IN to a LIKE.

IF EXISTS (SELECT 1
    FROM benutzer_rollen br
    JOIN doc_roll_header rh ON br.roll_id = rh.roll_id
    WHERE br.benutzername = 'Usr_X'
      AND rh.roll_text = 'Admin'
)
    SELECT 
      COUNT(*)
    FROM firmen_spezifisch f
    WHERE f.dok_dat_feld_86 = '671827194002' 
      AND f.dok_dat_feld_13 <> 'Ja' 
ELSE
    SELECT 
      COUNT(*)
    FROM firmen_spezifisch f
    WHERE f.dok_dat_feld_86 = '671827194002' 
      AND f.dok_dat_feld_13 <> 'Ja' 
      AND f.dok_dat_feld_2 LIKE 'GRP_[A-I]'
;

You also need an index:

doc_roll_header (roll_text, roll_id)