ReSQL.ru
     
powered by simpleCommunicator - 2.0.50     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / execute statement: как определить затраты на prepare ? (в trace-логе мало инфы)
19 сообщений из 19, страница 1 из 1
execute statement: как определить затраты на prepare ? (в trace-логе мало инфы)
    #37384446
Таблоид
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Таблоид
Участник
hi all

Дано:
1. Таблица t_list с двумя полями (id, f01), одно из которых ПК, в ней 50 тыс строк
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
recreate table t_list(id int primary key, f01 int);
recreate table t_temp(id int primary key, f01 int);
commit;

execute block as
declare n int= 50000 ;
begin
  while (n> 0 ) do
    insert into t_temp values(:n, rand()* 50000 ) returning :n- 1  into n;
  insert into t_list select * from t_temp order by rand();
  delete from t_temp;
end;
commit;

2. Скрипт с именем 'es_nobind.sql', выполняющий в цикле 10 вызовов execute statement с простым селектом из t_list одной строки. На каждой итерации формируется новое выражение, что вызывает, кончено же, лишние prepare:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
-- file = 'es_nobind.sql'
set stat on;
set echo on;
set term ^;
execute block as
declare stt varchar( 255 );
declare v int;
declare n int =  10 ; --50000;
begin
  while (n> 0 ) do begin
    stt='select f01 from t_list where id='||n;
    execute statement (:stt) into :v;
    n=n- 1 ;
  end
end^
set term ;^
set stat off;
commit;

3. Скрипт с именем 'es_bind.sql' выполняющий аналогичный цикл, но в нём уже используется привязка параметра и выражение препарится один раз:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
-- file = 'es_bind.sql'
set stat on;
set echo on;
set term ^;
execute block as
declare stt varchar( 255 );
declare v int;
declare n int =  10 ; --50000;
begin
  stt='select f01 from t_list where id=:a_id';
  while (n> 0 ) do begin
    execute statement (:stt) (a_id := :n) 
    into :v;
    n=n- 1 ;
  end
end^
set term ;^
set stat off;
commit;

4. Содержимое конфига трейса:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
<database %[\\/](employee|idx_under_load_test|test|test2|test3|test4|test5).fdb>
  enabled true
  log_statement_prepare true
  log_filename zaudit.log
  max_log_size  20 
  log_connections true
  log_transactions true
  log_statement_finish true
  print_perf true
  time_threshold  0 
</database>

Теперь запускаю ISQL и в нём ввожу на выполнение сначала es_nobind.sql, затем es_bind.sql.
Далее открываю лог трейса и вижу одинаковую статистику для ОБОИХ скриптов:
1) на первой итерации получилось 3 read, 13 fetches
2) на всех последующих итерациях - 1 reads, 5 fetches

При выполнении скрипта es_nobind.sql временнЫе затраты на препаре для первой итерации (select f01 from t_list where id=10) оказались 6 мс, для всех последующих (select f01 from t_list where id=9, 8, 7 etc) - 0 мс.
При выполнении скрипта es_bind.sql временнЫе затраты на препаре выражения select f01 from t_list where id=? составили 5 ms и выполнялись они, ес-сно, однократно.

Код: plaintext
......................................................................................

Теперь меняю количество итераций с 10 на 50'000, отрубаю трейс этой базы, перезапускаю ФБ.
Снова запускаю ISQL и в нём опять два скрипта.
Вижу следующее:
 
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
SQL> in es_bind.sql;
set term ^;
execute block as
declare stt varchar(255);
declare v int;
declare n int = 50000;
begin
  stt='select f01 from t_list where id=:a_id';
  while (n>0) do begin
    execute statement (:stt) (a_id := :n)
    into :v;
    n=n-1;
  end
end^
Current memory = 8917040
Delta memory = 29480
Max memory = 9006872
 Elapsed time= 2.06 sec 
Buffers = 1024
Reads = 48
Writes 0
 Fetches = 250087 
set term ;^
set stat off;
commit;

SQL> in es_nobind.sql;
in es_nobind.sql;
set stat on;
set echo on;
set term ^;
execute block as
declare stt varchar(255);
declare v int;
declare n int = 50000;
begin
  while (n>0) do begin
    stt='select f01 from t_list where id='||n;
    execute statement (:stt) into :v;
    n=n-1;
  end
end^
Current memory = 9022980
Delta memory = 124100
Max memory = 9034408
 Elapsed time= 12.18 sec 
Buffers = 1024
Reads = 0
Writes 0
 Fetches = 450039 
set term ;^
set stat off;
commit;


Понятно, что различие времени вып-я в 6 раз связано именно с prepare выражения, которое 50 тыс раз создается по-новой и подсовывается на выполнение в ES.

Непонятно другое: а откуда вылезло такое расхождение в числе фетчей на 50 тыс итерациях, если на 10 (десяти) итерациях его не было вообще ?
Вот фрагмент из трейса для препаре:
2011-08-08T00:37:05.8750 (428:020DE01C) PREPARE_STATEMENT
C:\1INSTALL\FIREBIRD\DATA\TEST3.FDB (ATT_16, SYSDBA:NONE, NONE, XNET:BALAHA)
C:\1INSTALL\FIREBIRD\FB_2_5\bin\isql.exe:804
(TRA_50189, CONCURRENCY | WAIT | READ_WRITE)

Statement 38:
-------------------------------------------------------------------------------
select f01 from t_list where id=10
6 ms

Почему здесь нет фетчей ? Разве ФБ не должен проверить хотя бы существование объекта (t_list) в БД, мои права на селект из него, наличие в нём полей f01 и ID ?

PS. В аттаче - трейс для первого прогона, где был цикл на 10 итераций.
...
Рейтинг: 0 / 0
08.08.2011, 00:56
| Ответить | Цитировать | Написать  
execute statement: как определить затраты на prepare ? (в trace-логе мало инфы)
    #37384570
Таблоид
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Таблоид
Участник
Таблоид откуда вылезло такое расхождение в числе фетчей на 50 тыс итерациях, если на 10 (десяти) итерациях его не было вообще ? Вру. Это в трейсе не было расхождений. А в ISQL очень даже и есть: 54 для es_bind.sql и 90 для es_nobind.sql.
Влад говорил ранее:
hvladТрейс оперирует индивидуальными для запроса счётчиками и измеряет только выполнение указанной операции
<...>
[ISQL оперирует] Глобальными, полученными от isc_database_info.
Причём первый раз он их берёт до isc_dsql_prepare, а второй - после isc_dsql_free_statement.
В общем, 2 hvlad : есть ли возможность показывать в трейсе затраты на prepare (значения reads, fetches, которые потребовались на него) ?
...
Рейтинг: 0 / 0
08.08.2011, 08:31
| Ответить | Цитировать | Написать  
execute statement: как определить затраты на prepare ? (в trace-логе мало инфы)
    #37384724
hvlad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
hvlad
Участник
Таблоидесть ли возможность показывать в трейсе затраты на prepare (значения reads, fetches, которые потребовались на него) ?Мой ответ тебе вряд ли понравится :)

Затраты на prepare учитываются в статистике системных запросов, которые в трейсе не показываются.
Соответственно я могу только пообещать добавить трассировку системных запросов.
Но они написаны на GDML и их текстов в движке нет, так что вряд ли эта информация будет кому-то сильно полезна (кроме разве что нас самих).
...
Рейтинг: 0 / 0
08.08.2011, 10:32
| Ответить | Цитировать | Написать  
execute statement: как определить затраты на prepare ? (в trace-логе мало инфы)
    #37384895
WildSery
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
WildSery
Участник
hvladих текстов в движке нет... а где?
...
Рейтинг: 0 / 0
08.08.2011, 11:45
| Ответить | Цитировать | Написать  
execute statement: как определить затраты на prepare ? (в trace-логе мало инфы)
    #37384974
hvlad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
hvlad
Участник
WildSeryhvladих текстов в движке нет... а где? В исходниках (.epp) до обработки препроцессором.
...
Рейтинг: 0 / 0
08.08.2011, 12:14
| Ответить | Цитировать | Написать  
execute statement: как определить затраты на prepare ? (в trace-логе мало инфы)
    #37385932
Фотография kdv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kdv
Участник
как тестировавший терабайтную базу, могу заявить, что основные затраты при prepare это скан кардинальности таблиц (pointer pages), поэтому, пока речь не идет о базе в 100 гиг и очень больших таблицах, беспокоиться по поводу затрат на prepare не очень нужно.
...
Рейтинг: 0 / 0
08.08.2011, 19:33
| Ответить | Цитировать | Написать  
execute statement: как определить затраты на prepare ? (в trace-логе мало инфы)
    #37385943
Таблоид
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Таблоид
Участник
kdvосновные затраты при prepare это скан кардинальности таблиц (pointer pages)да,у нас таблицы поменьше, конечно. Но ведь этот execute statement в цикле, а цикл может случиться разным (по числу итераций... ;-))
...
Рейтинг: 0 / 0
08.08.2011, 19:44
| Ответить | Цитировать | Написать  
Период между сообщениями больше года.
execute statement: как определить затраты на prepare ? (в trace-логе мало инфы)
    #39992266
ggreggory
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ggreggory
Гость
FB 3.0.5.33220

А есть информация, как долго сохраняется prepare для execute statement с параметрами - до конца транзакции, коннекта? И доступны ли запросы, препарированные execute statement в одной транзакции, другим транзакциям, коннектам?

И есть ли ограничение на кол-во препарированных запросов внутри процедуры/триггера, транзакции?

Интересуюсь с тем, чтобы понять, стоит ли переделывать существующие execute statement без параметров на варианты с параметрами с целью ускорения работы, или оставить как есть, раз prepare и так быстро работает.
...
Рейтинг: 0 / 0
25.08.2020, 14:05
| Ответить | Цитировать | Написать  
execute statement: как определить затраты на prepare ? (в trace-логе мало инфы)
    #39992274
Фотография Симонов Денис
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Симонов Денис
Участник
ggreggory,

препарированные запросы всегда привязаны к соединению, а не транзакции, так что

ggreggoryИ доступны ли запросы, препарированные execute statement в одной транзакции, другим транзакциям, коннектам?

другим коннектам точно нет. Насчёт остального не знаю точно, но скорее всего да. С внешними соединениями есть нюансы, они живут либо до конца текущей транзакции, либо сразу закрываются для автономок. Пул внешних соединений появился только в 4.0.
...
Рейтинг: 0 / 0
25.08.2020, 14:21
| Ответить | Цитировать | Написать  
execute statement: как определить затраты на prepare ? (в trace-логе мало инфы)
    #39992472
ggreggory
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ggreggory
Гость
Спасибо! Буду думать-тестить....
...
Рейтинг: 0 / 0
26.08.2020, 11:16
| Ответить | Цитировать | Написать  
execute statement: как определить затраты на prepare ? (в trace-логе мало инфы)
    #39992666
DmSer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
DmSer
Участник
От компонентов может зависеть. При использовании ibx препапированные запросы привязаны к транзакции.
...
Рейтинг: 0 / 0
26.08.2020, 19:17
| Ответить | Цитировать | Написать  
execute statement: как определить затраты на prepare ? (в trace-логе мало инфы)
    #39992710
YuRock
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
YuRock
Участник
DmSer
От компонентов может зависеть. При использовании ibx препапированные запросы привязаны к транзакции.
Хм. У меня - не были привязаны никогда, из коробки.
...
Рейтинг: 0 / 0
26.08.2020, 21:57
| Ответить | Цитировать | Написать  
execute statement: как определить затраты на prepare ? (в trace-логе мало инфы)
    #39992736
ggreggory
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ggreggory
Гость
DmSer,

Мой вопрос был только о prepare для execute statement, а не о prepare на стороне клиента. Пока, мне кажется, что prepare сохраняется вообще только для одного запроса. Ниже 3 тестовых процедуры.

TEST_1 содержит один запрос, у меня выполняется 62 миллисекунды
TEST_2 содержит два чередующихся запроса, 422 миллисекунды
TEST_3 содержит 10000 различных запросов, работает примерно то же время что и TEST_2

т.е. начиная с двух запросов нет никакой разницы во времени выполнения, prepare делается каждый раз, как встречается новый запрос.

create or alter procedure TEST_1
as
declare variable I integer;
declare variable TMP integer;
declare variable S varchar(1000);
begin
i = 0;
while (i <= 10000) do
begin
s = 'select 0 from RDB$DATABASE where ' || 0 || ' = ?';
i = i + 1;
execute statement (s) (0) into tmp;
end
end^

create or alter procedure TEST_2
as
declare variable I integer;
declare variable TMP integer;
declare variable S varchar(1000);
begin
i = 0;
while (i <= 10000) do
begin
s = 'select 0 from RDB$DATABASE where ' || bin_and(i, 1) || ' = ?';
i = i + 1;
execute statement (s) (bin_and(i, 1)) into tmp;
end
end^

create or alter procedure TEST_3
as
declare variable I integer;
declare variable TMP integer;
declare variable S varchar(1000);
begin
i = 0;
while (i <= 10000) do
begin
s = 'select 0 from RDB$DATABASE where ' || i || ' = ?';
i = i + 1;
execute statement (s) (i) into tmp;
end
end^
...
Рейтинг: 0 / 0
26.08.2020, 23:22
| Ответить | Цитировать | Написать  
execute statement: как определить затраты на prepare ? (в trace-логе мало инфы)
    #39992743
Фотография kdv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kdv
Участник
ggreggory,

гм. test2 и test3 отличаются только передачей параметра в exec statement (да и test1 тоже, что не соответствует вашему тексту). Но в во всех трех случаях оно 10к раз перепрепарируется.
Получается, идет сравнение скорости
where ' || bin_and(i, 1) || ' = ?';
...
execute statement (s) (bin_and(i, 1)) into tmp;

и
where ' || i || ' = ?';
...
execute statement (s) (i) into tmp;

В ФБ нет кэша препарированных запросов. Тем более в контексте процедуры. Если клиент может использовать хэндл препарированного запроса, то у ES никакого "хэндла" нет. Тем более, откуда ФБ должен догадаться, что в переменной s всё время один и тот же запрос? Он бы мог, если бы был кэш запросов вообще (по контрольной сумме запроса, например).
...
Рейтинг: 0 / 0
26.08.2020, 23:50
| Ответить | Цитировать | Написать  
execute statement: как определить затраты на prepare ? (в trace-логе мало инфы)
    #39992746
YuRock
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
YuRock
Участник
ggreggory,

У тебя ddl, тут вообще другая история, тёмная для меня. Я такие "запросы" стараюсь выполнять по отдельности в отдельных транзакциях, обрамляя коммитами.
...
Рейтинг: 0 / 0
26.08.2020, 23:52
| Ответить | Цитировать | Написать  
execute statement: как определить затраты на prepare ? (в trace-логе мало инфы)
    #39992762
ggreggory
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ggreggory
Гость
kdv
Но в во всех трех случаях оно 10к раз перепрепарируется.


А я пришёл к выводу, что в TEST_1 запрос препарируется единократно. А в TEST_2 и TEST_3 препарируется каждый раз, поэтому и возникает разница.

kdv

Получается, идет сравнение скорости
where ' || bin_and(i, 1) || ' = ?';
...
execute statement (s) (bin_and(i, 1)) into tmp;

и
where ' || i || ' = ?';
...
execute statement (s) (i) into tmp;


Вы можете проверить у себя эти две процедуры. Я не нашёл существенной разницы в скорости их работы. Но вот процедура TEST_1 работает в 7 раз быстрее, из чего я предположил, что в ней prepare делается только один раз.

kdv

В ФБ нет кэша препарированных запросов. Тем более в контексте процедуры. Если клиент может использовать хэндл препарированного запроса, то у ES никакого "хэндла" нет. Тем более, откуда ФБ должен догадаться, что в переменной s всё время один и тот же запрос? Он бы мог, если бы был кэш запросов вообще (по контрольной сумме запроса, например).


Вот что пишут про это в инструкции:

In previous versions, if EXECUTE STATEMENT occurred in a loop, the SQL statement would be prepared, executed and released upon every iteration. In Firebird 2.5 and above, such a statement is only prepared once, giving a huge performance benefit.
(ссылка https://www.firebirdsql.org/refdocs/langrefupd25-psql-execstat.html
...
Рейтинг: 0 / 0
27.08.2020, 01:13
| Ответить | Цитировать | Написать  
execute statement: как определить затраты на prepare ? (в trace-логе мало инфы)
    #39992769
hvlad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
hvlad
Участник
ggreggory,

До появления пула (в fb4) жизнь внешнего коннекта была привязана
- к локальной (юзерской) тр-ции, если внешняя тр-ция - COMMON (по-умолчанию), или
- к жизни самого запроса, если внешняя тр-ция - AUTONOMOUS.

У тебя - COMMON тр-ция, поэтому коннект, который использует ES, живёт до окончания юзерской тр-ции и может быть использован повторно.

Далее. Внешний коннект кеширует до 16 выполнявшихся запросов (сравнивается полный текст, как его передали в ES).
Но у тебя запросы SELECT, из которых нет фетчей - их нельзя повторно использовать, т.к. они активны.
Это жуткий пример как делать НЕ НАДО.
Посмотри мониторингом - сколько у тебя живых запросов после такого ES (до коммита тр-ции).

Ну и напоследок - трейс покажет и сколько раз был выполнен prepare, и сколько времени он занял.

PS не проверял
...
Рейтинг: 0 / 0
27.08.2020, 02:06
| Ответить | Цитировать | Написать  
execute statement: как определить затраты на prepare ? (в trace-логе мало инфы)
    #39992776
hvlad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
hvlad
Участник
Поспешил с критикой немного, всё там закрывается и повторно используется (по мере возможности).

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
-- 1
EXECUTE BLOCK
AS
DECLARE VARIABLE I   INTEGER;
DECLARE VARIABLE TMP INTEGER;
DECLARE VARIABLE S   VARCHAR(1000);
BEGIN
  I = 0;
  WHILE (I <= 10000) DO
  BEGIN
    S = 'select 0 from RDB$DATABASE where ' || 0 || ' = ?';
    I = I + 1;
    EXECUTE STATEMENT(S)(0)
      INTO TMP;
  END
END


Код: plaintext
1.
2.
3.
4.
Query Time
------------------------------------------------
Prepare       : 0.00 ms
Execute       : 62.00 ms
Avg fetch time: 0.00 ms


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
-- 2
EXECUTE BLOCK
AS
DECLARE VARIABLE I   INTEGER;
DECLARE VARIABLE TMP INTEGER;
DECLARE VARIABLE S   VARCHAR(1000);
BEGIN
  I = 0;
  WHILE (I <= 10000) DO
  BEGIN
    S = 'select 0 from RDB$DATABASE where ' || BIN_AND(I, 1) || ' = ?';
    I = I + 1;
    EXECUTE STATEMENT(S)(BIN_AND(I, 1))
      INTO TMP;
  END
END


Код: plaintext
1.
2.
3.
4.
5.
Query Time
------------------------------------------------
Prepare       : 0.00 ms
Execute       : 62.00 ms
Avg fetch time: 0.00 ms

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
-- 3
EXECUTE BLOCK
AS
DECLARE VARIABLE I   INTEGER;
DECLARE VARIABLE TMP INTEGER;
DECLARE VARIABLE S   VARCHAR(1000);
BEGIN
  I = 0;
  WHILE (I <= 10000) DO
  BEGIN
    S = 'select 0 from RDB$DATABASE where ' || I || ' = ?';
    I = I + 1;
    EXECUTE STATEMENT(S)(I)
      INTO TMP;
  END
END



Код: plaintext
1.
2.
3.
4.
5.
Query Time
------------------------------------------------
Prepare       : 0.00 ms
Execute       : 281.00 ms
Avg fetch time: 0.00 ms
...
Рейтинг: 0 / 0
27.08.2020, 02:23
| Ответить | Цитировать | Написать  
execute statement: как определить затраты на prepare ? (в trace-логе мало инфы)
    #39993053
ggreggory
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ggreggory
Гость
hvlad
Внешний коннект кеширует до 16 выполнявшихся запросов (сравнивается полный текст, как его передали в ES).


Спасибо за наводку! Теперь картина вырисовывается.

hvlad
всё там закрывается и повторно используется (по мере возможности).


Да, так и есть. Стал разбираться почему вчера у меня тайминг у 2-ой процедуры другой получился. Оказывается я не подтвердил транзакцию от предыдущего эксперимента и у меня просто исчерпалось кол-во запросов. Если транзакцию подтверждать, то всё работает точь-в-точь как вы написали.
...
Рейтинг: 0 / 0
27.08.2020, 16:30
| Ответить | Цитировать | Написать  
19 сообщений из 19, страница 1 из 1
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / execute statement: как определить затраты на prepare ? (в trace-логе мало инфы)
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=40&fpage=12&tid=1560267]:
 0ms
get settings:
 7ms
get forum list:
 16ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 42ms
get topic data:
 12ms
get forum data:
 6ms
get page messages:
 58ms
get tp. blocked users:
 1ms
others: 245ms
total:  395ms
созд. / загр.: 395ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]