Сегодняшние сообщения (вкл)
| Сообщения без ответа (откл)
Форум: Firebird, HQbird, InterBase
|
Тема: IEEE 754 (DECFLOAT)
|
|
|
Тема: FB 5.0 Производительность, оптимизация
|
Re: FB 5.0 Производительность, оптимизация [сообщение #4724 является ответом на сообщение #4720] |
Fri, 29 March 2024 00:45 |
hvlad
Сообщений: 295 Зарегистрирован: August 2022
|
Senior Member |
|
|
Я правильно понимаю, что есть некая процедура, которая в числе прочего вызывает с помощью EXEC STMT вышеуказанные запросы ?
И эти запросы в 5 выполняются не медленнее, чем в 2.5 ?
Т.е. всё сводится к процедуре, план которой для 5 не известен ?
План процедуры можно увидеть в MON$COMPILED_STATEMENTS, см например, тут
|
|
|
Re: FB 5.0 Производительность, оптимизация [сообщение #4725 является ответом на сообщение #4724] |
Fri, 29 March 2024 07:39 |
pastor
Сообщений: 55 Зарегистрирован: June 2022 Географическое положение: Калуга
|
Member |
|
|
так точно
1. готовлю воспроизводимую минимальную базу
2. посмотрю. в 2.5 в обычных запросах к временным таблицам внутри SP (в которой они набивались) не подхватывались индексы
было обойдено через ES
3. попробую еще и в редэксперте с профилированием
|
|
|
Re: FB 5.0 Производительность, оптимизация [сообщение #4728 является ответом на сообщение #4724] |
Fri, 29 March 2024 12:31 |
pastor
Сообщений: 55 Зарегистрирован: June 2022 Географическое положение: Калуга
|
Member |
|
|
меняем показания:
порезал код процедуры с 900 строк до 100.
остался последний ES,. тормоз остался
нашел четыре обращения к большим временным таблицам без ES
итого:
1. ES стали работать медленнее
есть две идентичные базы 2.5 и 5.0
в архиве - около 10 мб
прислать?
[Обновления: Fri, 29 March 2024 13:13] Известить модератора
|
|
|
Re: FB 5.0 Производительность, оптимизация [сообщение #4729 является ответом на сообщение #4721] |
Fri, 29 March 2024 13:57 |
pastor
Сообщений: 55 Зарегистрирован: June 2022 Географическое положение: Калуга
|
Member |
|
|
sim_84 писал(а) Thu, 28 March 2024 16:42Ты по пустым таблицам что ли планы и статистику приводишь?
Как выводить внутренние планы процедур в 5-ке я тебе могу подсказать. Но не когда они в ES.
Зачем делать cast внутри суммы?
1. нет. в основной таблице 500к + записей
джойнится сама с собой несколько раз
2. Влад подсказал
3. FB не в курсе что лежит в текстовом поле. так надо.
|
|
|
Re: FB 5.0 Производительность, оптимизация [сообщение #4730 является ответом на сообщение #4716] |
Fri, 29 March 2024 14:04 |
pastor
Сообщений: 55 Зарегистрирован: June 2022 Географическое положение: Калуга
|
Member |
|
|
[color=blue]ASQL[/color] = 'select
v7.ATTRIBUTE_VALUE as CODE,
v8.ATTRIBUTE_VALUE as NAME,
cast(v13.ATTRIBUTE_VALUE as numeric(15,2)) as PRICE,
sum( cast(v14.ATTRIBUTE_VALUE as numeric(15,1))) as AMOUNT--,
from
TMP$VALUES v0
join TMP$VALUES v7 on v7.ID_ITEM = v0.ID_ITEM and v7.ID_ATTRIBUTE = 7 -- код
join TMP$VALUES v8 on v8.ID_ITEM = v0.ID_ITEM and v8.ID_ATTRIBUTE = 8 -- название
join TMP$VALUES v10 on v10.ID_ITEM = v0.ID_ITEM and v10.ID_ATTRIBUTE = 10 -- группа услуг
join TMP$VALUES v13 on v13.ID_ITEM = v0.ID_ITEM and v13.ID_ATTRIBUTE = 13 -- цена
join TMP$VALUES v14 on v14.ID_ITEM = v0.ID_ITEM and v14.ID_ATTRIBUTE = 14 -- кол-во
where v0.ID_ATTRIBUTE = 0 and v0.ATTRIBUTE_VALUE = :OPER
and v7.ATTRIBUTE_VALUE = :S7
and v10.ATTRIBUTE_VALUE = :S10
group by 1, 2, 3
order by 1, 2, 3';
вызывается вот так (995 раз)
модельные параметры:
OPER = 'SALE'
S7 = '1003'
S10 = 'Бассейн'
DB = 'NOW';
-- услуги в группировке по коду/имени/цене
CNT_ASQL = CNT_ASQL + 1;
for execute statement (ASQL) (S7:=:S1, S10:=:S10, OPER:=:OPER)
into :S7, :S8, :S13, :S14
do begin
/*
STRINGS = (select first 1 s.MON$EXPLAINED_PLAN from MON$COMPILED_STATEMENTS s
where S.MON$SQL_TEXT containing 'TMP$VALUES');
suspend;
*/
end -- услуги в группировке по коду/имени/цене
DE = 'NOW';
STRINGS = '<!-- "'|| :S1|| '" "'|| :S2|| '" '||datediff( millisecond, DB, DE)||' -->'; suspend;
FB50
изнутри процедуры
<!-- "1003" "3083" 130.0 -->
Select Expression
-> Sort (record length: 2628, key length: 1044)
-> Aggregate
-> Refetch
-> Sort (record length: 1152, key length: 1044)
-> Filter
-> Hash Join (inner)
-> Hash Join (inner)
-> Hash Join (inner)
-> Hash Join (inner)
-> Hash Join (inner)
-> Filter
-> Table "TMP$VALUES" as "V0" Access By ID
-> Bitmap
-> Index "TMP$VALUES_ATT" Range Scan (full match)
-> Record Buffer (record length: 561)
-> Filter
-> Table "TMP$VALUES" as "V7" Access By ID
-> Bitmap
- > Index "TMP$VALUES_ATT" Range Scan (full match)
-> Record Buffer (record length: 561)
-> Filter
-> Table "TMP$VALUES" as "V8" Access By ID
-> Bitmap
-> Index "TMP$VALUES_ATT" Range Scan (full match)
-> Record Buffer (record length: 561)
-> Filter
-> Table "TMP$VALUES" as "V10" Access By ID
-> Bitmap
-> Index "TMP$VALUES_ATT" Range Scan (full match)
-> Record Buffer (record length: 561)
-> Filter
-> Table "TMP$VALUES" as "V13" Access By ID
-> Bitmap
-> Index "TMP$VALUES_ATT" Range Scan (full match)
-> Record Buffer (record length: 561)
-> Filter
-> Table "TMP$VALUES" as "V14" Access By ID
-> Bitmap
-> Index "TMP$VALUES_ATT" Range Scan (full match)
отдельно, с теми же параметрами
------ Performance info ------
Prepare time = 0ms
Execute time = 172ms
Avg fetch time = 172,00 ms
Current memory = 456 180 224
Max memory = 538 854 576
Memory buffers = 99 999
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 255 640
PLAN SORT (SORT (HASH (HASH (HASH (HASH (HASH (V0 INDEX (TMP$VALUES_ATT), V7 INDEX (TMP$VALUES_ATT)), V8 INDEX (TMP$VALUES_ATT)), V10 INDEX (TMP$VALUES_ATT)), V13 INDEX (TMP$VALUES_ATT)), V14 INDEX (TMP$VALUES_ATT))))
------------------------------------------------------------ --------------------
------------------------------------------------------------ --------------------
------------------------------------------------------------ --------------------
FB25
изнутри процедуры
<!-- "1003" "3083" 85 -->
плана, есс-но нет
отдельно, с теми же параметрами
План
------------------------------------------------------------ --------------------
PLAN SORT (SORT (JOIN (V0 INDEX (TMP$VALUES_AV), V7 INDEX (TMP$VALUES), V8 INDEX (TMP$VALUES), V10 INDEX (TMP$VALUES), V13 INDEX (TMP$VALUES), V14 INDEX (TMP$VALUES))))
------ Performance info ------
Prepare time = 0ms
Execute time = 125ms
Avg fetch time = 125,00 ms
Current memory = 449 807 208
Max memory = 463 328 096
Memory buffers = 99 999
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 285 675
|
|
|
|
Re: FB 5.0 Производительность, оптимизация [сообщение #4732 является ответом на сообщение #4730] |
Fri, 29 March 2024 14:48 |
sim_84
Сообщений: 291 Зарегистрирован: June 2022
|
Senior Member |
|
|
Таблицы временные - оптимизатору не известны ни кардинальности, ни нормальные селективности индексов, вот и фигачит везде HASH JOIN.
Если бы таблицы были постоянными у оптимизатора 5-ки было бы выше шансов построить оптимальный план
|
|
|
|
Re: FB 5.0 Производительность, оптимизация [сообщение #4734 является ответом на сообщение #4732] |
Fri, 29 March 2024 14:59 |
pastor
Сообщений: 55 Зарегистрирован: June 2022 Географическое положение: Калуга
|
Member |
|
|
sim_84 писал(а) Fri, 29 March 2024 14:48Таблицы временные - оптимизатору не известны ни кардинальности, ни нормальные селективности индексов, вот и фигачит везде HASH JOIN.
Если бы таблицы были постоянными у оптимизатора 5-ки было бы выше шансов построить оптимальный план
я раз в два года беспокою Влада по этому поводу.
при использовании ES в 2.5 жизнь наладилась
после чесания репы, наладилась еще в 2.5 раза
в 5.0 немножко вернулась взад, на исходную
т.к. сбор отчетов через временные таблицы становится мейнстримом (кубы для нищебродов ), то хочется найти пусть и обходное, но решение
|
|
|
Re: FB 5.0 Производительность, оптимизация [сообщение #4735 является ответом на сообщение #4734] |
Fri, 29 March 2024 15:05 |
hvlad
Сообщений: 295 Зарегистрирован: August 2022
|
Senior Member |
|
|
pastor писал(а) Fri, 29 March 2024 13:59sim_84 писал(а) Fri, 29 March 2024 14:48Таблицы временные - оптимизатору не известны ни кардинальности, ни нормальные селективности индексов, вот и фигачит везде HASH JOIN.
Если бы таблицы были постоянными у оптимизатора 5-ки было бы выше шансов построить оптимальный план
я раз в два года беспокою Влада по этому поводу.
при использовании ES в 2.5 жизнь наладилась
после чесания репы, наладилась еще в 2.5 раза
в 5.0 немножко вернулась взад, на исходную
т.к. сбор отчетов через временные таблицы становится мейнстримом (кубы для нищебродов ), то хочется найти пусть и обходное, но решение
А насколько большие эти GTT ?
|
|
|
Re: FB 5.0 Производительность, оптимизация [сообщение #4736 является ответом на сообщение #4735] |
Fri, 29 March 2024 15:10 |
pastor
Сообщений: 55 Зарегистрирован: June 2022 Географическое положение: Калуга
|
Member |
|
|
hvlad писал(а) Fri, 29 March 2024 15:05pastor писал(а) Fri, 29 March 2024 13:59sim_84 писал(а) Fri, 29 March 2024 14:48Таблицы временные - оптимизатору не известны ни кардинальности, ни нормальные селективности индексов, вот и фигачит везде HASH JOIN.
Если бы таблицы были постоянными у оптимизатора 5-ки было бы выше шансов построить оптимальный план
я раз в два года беспокою Влада по этому поводу.
при использовании ES в 2.5 жизнь наладилась
после чесания репы, наладилась еще в 2.5 раза
в 5.0 немножко вернулась взад, на исходную
т.к. сбор отчетов через временные таблицы становится мейнстримом (кубы для нищебродов ), то хочется найти пусть и обходное, но решение
А насколько большие эти GTT ?
26 record(s) was(were) inserted into TMP$ATTRIBUTES
26536 record(s) was(were) inserted into TMP$ITEMS
563640 record(s) was(were) inserted into TMP$VALUES
70 record(s) was(were) inserted into TMP$ATTRIBUTE_AGREGATES
2 record(s) was(were) inserted into TMP$DIMENSIONS
26536 record(s) was(were) inserted into TMP$FACTS
это за месяц
около 1000 проходов - 2.5 минуты на АИ50 и 1 мин на АИ25
за год соответственно 6 млн записей
27 мин и 11 мин соответственно
[Обновления: Fri, 29 March 2024 15:10] Известить модератора
|
|
|
Re: FB 5.0 Производительность, оптимизация [сообщение #4737 является ответом на сообщение #4736] |
Fri, 29 March 2024 15:16 |
hvlad
Сообщений: 295 Зарегистрирован: August 2022
|
Senior Member |
|
|
pastor писал(а) Fri, 29 March 2024 14:10hvlad писал(а) Fri, 29 March 2024 15:05А насколько большие эти GTT ?
26 record(s) was(were) inserted into TMP$ATTRIBUTES
26536 record(s) was(were) inserted into TMP$ITEMS
563640 record(s) was(were) inserted into TMP$VALUES
70 record(s) was(were) inserted into TMP$ATTRIBUTE_AGREGATES
2 record(s) was(were) inserted into TMP$DIMENSIONS
26536 record(s) was(were) inserted into TMP$FACTS
это за месяц
около 1000 проходов - 2.5 минуты на АИ50 и 1 мин на АИ25
за год соответственно 6 млн записей
27 мин и 11 мин соответственно
Ещё один вопрос - сколько времени займёт пересчёт статики для всех индексов TMP$VALUES ?
|
|
|
|
|
Re: FB 5.0 Производительность, оптимизация [сообщение #4740 является ответом на сообщение #4732] |
Fri, 29 March 2024 15:22 |
SD
Сообщений: 329 Зарегистрирован: August 2022
|
Senior Member |
|
|
sim_84 писал(а) Fri, 29 March 2024 12:48Таблицы временные - оптимизатору не известны ни кардинальности, ни нормальные селективности индексов, вот и фигачит везде HASH JOIN.
Обычно это обходилось одноразовым пересчётом статистики индексов при наполненной временной таблице. Сохранённая селективность индекса после этого получала какое-то значение и с тех пор могла использоваться оптимизатором.
Цитата: сбор отчетов через временные таблицы становится мейнстримом (кубы для нищебродов), то хочется найти пусть и обходное, но решение
"Кубы для нищебродов" это хранимые агрегаты. Временные таблицы это костыль для MS SQL.
|
|
|
Re: FB 5.0 Производительность, оптимизация [сообщение #4741 является ответом на сообщение #4737] |
Fri, 29 March 2024 15:25 |
sim_84
Сообщений: 291 Зарегистрирован: June 2022
|
Senior Member |
|
|
hvlad писал(а) Fri, 29 March 2024 15:16
Ещё один вопрос - сколько времени займёт пересчёт статики для всех индексов TMP$VALUES ?
А что на временной таблице можно пересчитать статистику индексов и использовать её в той же транзакции?
Теоретически на ON COMMIT PRESERVE ROWS можно в разных транзакциях сделать заливку, пересчёт статистики и сам запрос.
Но в ON COMMIT DELETE ROWS, да ещё в той же транзакции/блоке это вообще реально?
|
|
|
Re: FB 5.0 Производительность, оптимизация [сообщение #4742 является ответом на сообщение #4737] |
Fri, 29 March 2024 15:25 |
pastor
Сообщений: 55 Зарегистрирован: June 2022 Географическое положение: Калуга
|
Member |
|
|
hvlad писал(а) Fri, 29 March 2024 15:16Ещё один вопрос - сколько времени займёт пересчёт статики для всех индексов TMP$VALUES ?
сделать в ES? в отдельной транзакции?
set statistics index TMP$VALUES
------ Performance info ------
Prepare time = 16ms
Execute time = 0ms
Current memory = 454 119 088
Max memory = 538 743 520
Memory buffers = 99 999
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 32
|
|
|
|
Re: FB 5.0 Производительность, оптимизация [сообщение #4744 является ответом на сообщение #4739] |
Fri, 29 March 2024 15:30 |
pastor
Сообщений: 55 Зарегистрирован: June 2022 Географическое положение: Калуга
|
Member |
|
|
create global temporary table TMP$VALUES(
ID_ITEM ZLS$FK,
ID_ATTRIBUTE ZLS$FK,
ATTRIBUTE_VALUE ZLS$COMMENT,
constraint TMP$VALUES
primary key(ID_ITEM, ID_ATTRIBUTE),
constraint TMP$VALUES_ITEM
foreign key (ID_ITEM)
references TMP$ITEMS (ID)
on delete CASCADE,
constraint TMP$VALUES_ATT
foreign key (ID_ATTRIBUTE)
references TMP$ATTRIBUTES (ID)
on delete CASCADE
)
on commit delete rows
^
commit^
comment on table TMP$VALUES is 'Значения атрибутов экземпляра # !'^
comment on column TMP$VALUES.ID_ITEM is 'Экземпляр'^
comment on column TMP$VALUES.ID_ATTRIBUTE is 'Атрибут'^
comment on column TMP$VALUES.ATTRIBUTE_VALUE is 'Значение атрибута'^
comment on index TMP$VALUES is 'Первичный ключ'^
comment on index TMP$VALUES_ITEM is 'Экземпляр'^
comment on index TMP$VALUES_ATT is 'Атрибут'^
create index TMP$VALUES_AV
on TMP$VALUES( ID_ATTRIBUTE, ATTRIBUTE_VALUE)
^
comment on index TMP$VALUES_AV is 'Значения атрибутов'^
|
|
|
Re: FB 5.0 Производительность, оптимизация [сообщение #4745 является ответом на сообщение #4737] |
Fri, 29 March 2024 15:33 |
pastor
Сообщений: 55 Зарегистрирован: June 2022 Географическое положение: Калуга
|
Member |
|
|
hvlad писал(а) Fri, 29 March 2024 15:16
Ещё один вопрос - сколько времени займёт пересчёт статики для всех индексов TMP$VALUES ?
DB = 'NOW';
execute statement('set statistics index TMP$VALUES');
DE = 'NOW';
STRINGS = '<!-- set statistics index TMP$VALUES '||datediff( millisecond, DB, DE)||' -->'; suspend;
результат
('<!-- sales facts [26450] 181.0 -->');
('<!-- returns facts [86] 164.0 -->');
('<!-- set statistics index TMP$VALUES 0.0 -->');
('<!-- "1003 135.0ms -->');
|
|
|
Re: FB 5.0 Производительность, оптимизация [сообщение #4746 является ответом на сообщение #4742] |
Fri, 29 March 2024 15:44 |
hvlad
Сообщений: 295 Зарегистрирован: August 2022
|
Senior Member |
|
|
pastor писал(а) Fri, 29 March 2024 14:25hvlad писал(а) Fri, 29 March 2024 15:16Ещё один вопрос - сколько времени займёт пересчёт статики для всех индексов TMP$VALUES ?
сделать в ES? в отдельной транзакции?
Тогда в отдельной тр-ции нет смысла. Как и в ES
pastorset statistics index TMP$VALUES
------ Performance info ------
Prepare time = 16ms
Execute time = 0ms
Current memory = 454 119 088
Max memory = 538 743 520
Memory buffers = 99 999
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 32 Статистика пересчитывается по коммиту, т.е. тут нужно в *одной* тр-ции:
- наполнить GTT данными
- выполнить SET STATS для каждого интересующего нас индекса
- выполнить коммит и смотреть на его ран-тайм статистику
|
|
|
|
Re: FB 5.0 Производительность, оптимизация [сообщение #4748 является ответом на сообщение #4747] |
Fri, 29 March 2024 15:50 |
pastor
Сообщений: 55 Зарегистрирован: June 2022 Географическое положение: Калуга
|
Member |
|
|
select
v7.ATTRIBUTE_VALUE as CODE,
v8.ATTRIBUTE_VALUE as NAME,
cast(v13.ATTRIBUTE_VALUE as numeric(15,2)) as PRICE,
sum( cast(v14.ATTRIBUTE_VALUE as numeric(15,1))) as AMOUNT--,
from
TMP$VALUES v0
join TMP$VALUES v7 on v7.ID_ITEM = v0.ID_ITEM and v7.ID_ATTRIBUTE = 7 -- код
join TMP$VALUES v8 on v8.ID_ITEM = v0.ID_ITEM and v8.ID_ATTRIBUTE = 8 -- название
join TMP$VALUES v10 on v10.ID_ITEM = v0.ID_ITEM and v10.ID_ATTRIBUTE = 10 -- группа услуг
join TMP$VALUES v13 on v13.ID_ITEM = v0.ID_ITEM and v13.ID_ATTRIBUTE = 13 -- цена
join TMP$VALUES v14 on v14.ID_ITEM = v0.ID_ITEM and v14.ID_ATTRIBUTE = 14 -- кол-во
where v0.ID_ATTRIBUTE = 0 and v0.ATTRIBUTE_VALUE = :OPER
and v7.ATTRIBUTE_VALUE = :S7
and v10.ATTRIBUTE_VALUE = :S10
group by 1, 2, 3
PLAN JOIN (V0 INDEX (TMP$VALUES_AV), V7 INDEX (TMP$VALUES), V8 INDEX (TMP$VALUES), V10 INDEX (TMP$VALUES), V13 INDEX (TMP$VALUES), V14 INDEX (TMP$VALUES))
order by 1, 2, 3
дало 170->106 ms в отдельном запросе
при попытке вставить в ES
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 22, column 30.
group.
<Missing arg #1 - possibly status vector overflow>.
-----------------------------------------------------------------------------------------
SQLCODE: -104
SQLSTATE: 42000
GDSCODE: 335544569
|
|
|
Текущее время: Fri Mar 29 15:51:56 GMT+3 2024
Общее время, затраченное на создание страницы: 0.01196 секунд
|