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