CURRENT OF vs поиск по ключу

Какое же утро без хорошего эксперимента. Берем встроенный сервер Firebird 2.5.3 и чистую БД:

Page size               8192 
ODS version             11.2 
Page buffers            75 
Database dialect        3 
Attributes              force write 

Создаем две идентичных по структуре таблицы:

create table t1 (i integer not null)
create table t2 (i integer not null)

Одинаково заполняем каждую из них миллионом случайных значений:

execute block
as
  declare variable i integer = 0;
  declare variable v integer = 0;
begin
  while (i < 1000000) do
  begin
    v = round(rand() * 2000000000);
    insert into t1 values (:v);
    insert into t2 values (:v);
    i = :i + 1;
  end
end

Для таблицы t1 создаем индекс:

create index t1x on t1 (i)

Делаем коммит транзакции и переподключение к базе данных. Организуем скан таблицы t1 и случайным образом удаляем четверть записей через поиск по индексированному полю:

execute block
as
  declare variable i integer;
begin
  for select i from t1 into :i
  do
  begin
    if (rand() < 0.25) then
      delete from t1 where i = :i;
  end
end

Статистика выполнения:

Execute       : 10 717.00 ms
Read          : 239 855
Writes        : 6 250
Fetches       : 4 264 681
Marks         : 250 286
IR            : 250 286
NIR           : 999 934
Deletes       : 250 286

Не совсем понятно почему NIR не миллион. Все остальное вполне логично. Коммит транзакции и еще раз удаляем четверть, теперь от оставшихся записей:

Execute       : 582 243.00 ms
Read          : 414 519
Writes        : 246 390
Fetches       : 5 712 020
Marks         : 944 206
IR            : 187 214
NIR           : 749 693 
Deletes       : 187 214
Expunges      : 250 286

Почти 10 минут! Обратите внимание на сумасшедшее количество Writes и Marks. Сборка мусора и перестроение индекса?

Далее, берем таблицу t2 и удаляем из нее четверть записей, но с помощью курсора и конструкции CURRENT OF:

execute block
as
  declare variable c cursor for
    (select i from t2);
  declare variable i integer;
begin
  open c;
  while (1=1) do
  begin
    fetch c into :i;
    if (row_count = 0) then
      leave;
    if (rand() < 0.25) then
      delete from t2 where current of c;
  end
  close c;
end

Статистика:

Execute       : 8 362.00 ms
Read          : 6 138
Writes        : 6 064
Fetches       : 2 762 582
Marks         : 250 100
NIR           : 1 000 000
Deletes       : 250 100

Обратите внимание, насколько меньше чтений по сравнению с удалением по индексированному полю. Выполнение на 20% быстрее за счет того, что не надо дергать индекс.

Коммит транзакции и еще раз удаляем четверть записей:

Execute       : 8 252.00 ms
Read          : 6 137
Writes        : 6 067
Fetches       : 3 587 315
Marks         : 693 789
NIR           : 749 900
Deletes       : 187 455
Expunges      : 250 100

Выполнился даже быстрее чем первый запрос, несмотря на сборку мусора, и в 70 (!!!) раз быстрее чем запрос с удалением по индексированному полю. Непонятно почему так велико значение Marks, ведь удаляется всего 187 455 записей.

Подсчитаем количество записей в первой таблице:

select count(*) from t1

Статистика:

Execute       : 493 697.00 ms
Read          : 181 339
Writes        : 181 084
Fetches       : 3 009 620
Marks         : 561 642
NIR           : 562 500
Expunges      : 187 214

Все ясно, сборка мусора напоролась на индекс. 8 минут на кофе. Теперь считаем во второй:

select count(*) from t2

Статистика:

Execute       : 6 443.00 ms
Read          : 6 137
Writes        : 6 064
Fetches       : 2 261 901
Marks         : 374 910
NIR           : 562 445
Expunges      : 187 455

Упс! Мы ожидали увидеть сравнимые цифры по времени выполнения для первой и второй таблицы, но разница составила 76 (!!!) раз. Все из-за наличия в первой таблице индекса, который при подсчете количества никак не используется, но катастрофически тормозит сборку мусора.

Попытаемся выяснить как влияет наличие индекса на выполнение операции CURRENT OF. Возвращаемся к исходной базе данных. Будем удалять записи из таблицы t1 с помощью конструкции CURRENT OF:

Execute       : 8 050.00 ms
Read          : 6 138
Writes        : 6 064
Fetches       : 2 765 717
Marks         : 251 145
NIR           : 1 000 000
Deletes       : 251 145

В одно время с удалением из таблицы, по которой нет индекса.

Второй цикл удаления:

Execute       : 688 480.00 ms
Read          : 240 628
Writes        : 240 341
Fetches       : 4 592 695
Marks         : 945 817
NIR           : 748 855
Deletes       : 186 248
Expunges      : 251 145

Самое большое время, которое нам пришлось наблюдать сегодня. Сборка мусора и индекс явно не дружат друг с другом.

Для оценки влияния сборки мусора повторим все операции с самого начала на исходной базе данных с флагом подключения no_garbage_collect. Первое удаление из таблицы t1 (поиск по индексированному полю):

Execute       : 12 418.00 ms
Read          : 239 735
Writes        : 6 231
Fetches       : 4 260 434
Marks         : 249 809
NIR           : 999 939
IR            : 249 809
Deletes       : 249 809

Странно, но NIR снова не миллион. Куда деваются около шестидесяти чтений непонятно.

Второе удаление:

Execute       : 9 563.00 ms
Read          : 181 842
Writes        : 6 189
Fetches       : 3 702 914
Marks         : 187 849
NIR           : 750 162
IR            : 187 849
Deletes       : 187 849

Записей стало меньше и время сканирования и удаления уменьшилось соответственно.

Для второй таблицы удаление через курсор. Первый проход:

Execute       : 8 112.00 ms
Read          : 6 138
Writes        : 6 064
Fetches       : 2 764 184
Marks         : 250 634
NIR           : 1 000 000
Deletes       : 250 634

Второй:

Execute       : 8 034.00 ms
Read          : 6 137
Writes        : 6 064
Fetches       : 2 574 131
Marks         : 187 283
NIR           : 749 366
Deletes       : 187 283

Обратите внимание, что цифры идентичны тем, которые мы имели при включенной сборке мусора. Т.е. нет индекса и сборка мусора не проблема.

Получаем количество записей для первой таблицы:

Execute       : 546.00 ms
Read          : 6 137
Writes        : 2
Fetches       : 2 012 281
Marks         : 0
NIR           : 562 342

Для второй:

Execute       : 531.00 ms
Read          : 6 137
Writes        : 2
Fetches       : 2 012 281
Marks         : 0
NIR           : 562 083

Выводы:

• Нижесказанное применимо к частным случаям массовой обработки данных.
• Если по таблице нет индексов, то включение/выключение сборки мусора практически не влияет на производительность.
• Если по таблице созданы индексы, то сборка мусора способна радикально, в десятки и сотни раз, замедлить ход процесса.
• В нашем примере CURRENT OF был на 20-25% быстрее чем поиск по индексированному полю.
• CURRENT OF может применяться на таблицах, где индексов нет вообще. Совершенно очевидно, что удаление с поиском по неиндесированному полю выполнялось бы в нашем случае часами, если не сутками.

Так и осталось загадкой, почему количество неиндексированных чтений при скане таблицы с индексом дважды получилось меньше правильного по теории миллиона.