The following query can be combined with various filters on Twinfield. However, the Twinfield-specific optimizations do not always lead to the desired performance improvements:
Base query
set use-http-disk-cache@tfd false
set use-http-memory-cache@tfd false
select count(*)
from GeneralLedgerDetailsV3@tfd
where fin_trs_line_dim1type = 'PNL'
... additional filter ...
Filter on specific year
The filter on specific year works fine, 31 seconds for 28 Twinfield offices:
and fin_trs_head_year = 2021
Filter on year range
The filter on specific year works not so fine, 85 seconds for 28 Twinfield offices:
and fin_trs_head_year >= 2021
Expectation: better server-side filtering for better performance.
Filter on year OR
The filter on specific years works not so fine, 80 seconds for 28 Twinfield offices:
and ( fin_trs_head_year = 2021 or fin_trs_head_year = 2022 )
Expectation: better server-side filtering for better performance, see also IN.
Filter on year IN
The filter on specific years works not so fine, 80 seconds for 28 Twinfield offices:
and fin_trs_head_year in (2021, 2022)
or
and fin_trs_head_year in (2021, 2024, 2027)
Expectation: better server-side filtering for better performance, see also OR.
The software might rewrite a consecutive list to a between for Twinfield, or two separate queries whose results are union-ed.