Background why I am doing this.
With the “Global” property in table1, the values from table3 should always be loaded from the main in table2 (there can only ever be one main in table2).
Otherwise if not “Global” read as usual.
I have packed this statement into a view to manipulate the access to table3 in the program. (I didn't want to have to adapt everything in Delphi)
This also works as desired, but a bit slow due to the multiple reads which in my opinion are unnecessary.
Is there a way to make it so that only 314 or 290 reads are made for the example?
If T1. “GLOBAL” is true, for example, you do not actually need to execute the condition if T1. “GLOBAL” is false.
select T1.ID as T1_ID,
T2.ID as T2_ID,
T3.WERT
from TABLE1 T1
cross join TABLE2 T2
inner join TABLE3 T3 on T3.T1_ID = T1.ID
-- 604 reads
( ( T1."GLOBAL" and
( T3.T2_ID = ( select T2_TEMP.ID
from TABLE2 T2_TEMP
where T2_TEMP.MAIN))) or
( not T1."GLOBAL" and
( T3.T2_ID = T2.ID)))
-- 314 reads
( T3.T2_ID = ( select T2_TEMP.ID
from TABLE2 T2_TEMP
where T2_TEMP.MAIN))
-- 290 reads
( T3.T2_ID = T2.ID)
-- iif() 5264 reads (If at least one of the True or False values in iif is a magic number then it works as I want. Either 314 or 290 reads.)
T3.T2_ID = iif( T1."GLOBAL", ( select T2_TEMP.ID
from TABLE2 T2_TEMP
where T2_TEMP.MAIN), T2.ID)
where T1.ID = 111 and
T2.ID = 20
Background why I am doing this. With the “Global” property in table1, the values from table3 should always be loaded from the main in table2 (there can only ever be one main in table2). Otherwise if not “Global” read as usual. I have packed this statement into a view to manipulate the access to table3 in the program. (I didn't want to have to adapt everything in Delphi)
This also works as desired, but a bit slow due to the multiple reads which in my opinion are unnecessary.
Is there a way to make it so that only 314 or 290 reads are made for the example? If T1. “GLOBAL” is true, for example, you do not actually need to execute the condition if T1. “GLOBAL” is false.