Мой пример тоже можно оформить в процедуре
Ой, не хочю спорить, но вот делал тесты
Where ?ParamStr Containing '~'||T.ID||'~'
работает в сто раз тормознее чем с ES.
ЕS идет по индексу ~12s на 7млн записей, а containing по натуралу 1m 33s
Так что здесь ЕS cамый смак
1:
Query
------------------------------------------------
select
sum(V.ID)
from TB_REPORT_VALUES V
where V.TEMPL_INDEX_ID in (1128624, 1128144, 1128263, 1128260)
Plan
------------------------------------------------
PLAN (V INDEX (RDB$FOREIGN18, RDB$FOREIGN18, RDB$FOREIGN18, RDB$FOREIGN18))
Adapted Plan
------------------------------------------------
PLAN (V INDEX (FK_INDEX, FK_INDEX, FK_INDEX, FK_INDEX))
Query Time
------------------------------------------------
Prepare : 16,00 ms
Execute : 7 738,00 ms
Avg fetch time: 7 738,00 ms
Memory
------------------------------------------------
Current: 9 501 804
Max : 9 635 096
Buffers: 2 048
Operations
------------------------------------------------
Read : 16 650
Writes : 0
Fetches: 43 066
Enchanced Info:
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| Table Name | Records | Indexed | Non-Indexed | Updates | Deletes | Inserts |
| | Total | reads | reads | | | |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| TB_REPORT_VALUES| 0 | 21511 | 0 | 0 | 0 | 0 |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
INDEX RDB$FOREIGN18 ON FIELD (TEMPL_INDEX_ID) ASCENDING STATISTIC: 0.00047415838
2:
Query
------------------------------------------------
select
sum(V.ID)
from TB_REPORT_VALUES V
where '1128624 1128144 1128263 1128260' containing V.TEMPL_INDEX_ID
Plan
------------------------------------------------
PLAN (V NATURAL)
Query Time
------------------------------------------------
Prepare : 0,00 ms
Execute : 93 928,00 ms
Avg fetch time: 93 928,00 ms
Memory
------------------------------------------------
Current: 9 097 400
Max : 9 635 096
Buffers: 2 048
Operations
------------------------------------------------
Read : 186 792
Writes : 0
Fetches: 15 069 587
Enchanced Info:
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| Table Name | Records | Indexed | Non-Indexed | Updates | Deletes | Inserts |
| | Total | reads | reads | | | |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+
| TB_REPORT_VALUES| 0 | 0 | 7348099 | 0 | 0 | 0 |
+--------------------------+-----------+-----------+-------------+---------+---------+---------+