sqlsql-serverquery-optimization

SQL Server does not short circuit static query


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.com/pastetheplan/?id=FeTZ3hPwZy


Solution

  • 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)