Тесты вариантов реализации проверки существования записи при ее обновлении

Alex Cherednichenko, 20.10.2001, последнее обновление – 26.04.2007


Задача

Сравнить относительную производительность 3-х вариантов реализации вставки или обновления записи (если записи нет, то Insert. Если она есть, то Update).
 

Исходные

Имеется тестовая таблица T_INTERNAL (диалект 3. Для проверки в диалекте 1 замените TIMESTAMP на DATE)
CREATE TABLE T_INTERNAL (
   ID INTEGER NOT NULL,
   TSTAMP TIMESTAMP);

ALTER TABLE T_INTERNAL
   ADD CONSTRAINT PK_T_INTERNAL PRIMARY KEY (ID);

Таблица заполнена случайными данными. Количество записей в таблице – 100 000. Для генерации данных использована процедура:
(функция GetRandom находится в библиотеке randomudf.zip)
CREATE PROCEDURE FILL_INTERNAL
AS
   DECLARE VARIABLE I INTEGER;
   DECLARE VARIABLE R INTEGER;
begin
   I=0; 
   R=GETRANDOM(999999);
   WHILE (:I<100000) DO
      BEGIN
         WHILE (EXISTS(SELECT ID FROM T_INTERNAL WHERE ID=:R))
            DO R=GETRANDOM(999999);
            INSERT INTO T_INTERNAL(ID) VALUES(:R);
            I=:I+1;
      END
end;

Имеется внешняя таблица T_EXTERNAL
CREATE TABLE T_EXTERNAL EXTERNAL 'G:\EXTERNAL.TXT' (
   ID CHAR(10),
   TSTAMP CHAR(24),
   CRLF CHAR(2));

Количество записей, тоже 100 000. Процент совпадения ключей T_EXTERNAL и T_INTERNAL изменялся от 30% до 77%.

Для заполнения таблицы использовались 2 процедуры:
  1. формирует 100 000 записей, из которых N являются выборкой из T_INTERNAL, а остальные (100 000 - N) _гарантировано_ отсутствуют в ней.
CREATE PROCEDURE SELECT_RANDOM(N INTEGER)
   RETURNS (ID INTEGER)
AS
   DECLARE VARIABLE I INTEGER;
   DECLARE VARIABLE K INTEGER;
BEGIN
   K=0;
   FOR SELECT T.ID, GETRANDOM(999999)
   FROM T_INTERNAL T ORDER BY 2
   INTO :ID,:I
   DO
      IF (K < N) THEN
         BEGIN
            SUSPEND;
            K=:K+1;
         END
      SELECT MAX(ID) FROM T_INTERNAL INTO :I;
      WHILE (K < 100000) DO
         BEGIN
            SELECT (:I+GETRANDOM(999999)) FROM RDB$DATABASE INTO :ID;
            SUSPEND;
            K=:K+1;
         END
END;
  1. "перемешивает" данные, сформированные из процедуры SELECT_RANDOM случайным образом, и заливает их во внешнюю таблицу T_EXTERNAL
CREATE PROCEDURE FILL_EXTERNAL(N INTEGER)
AS
   DECLARE VARIABLE ID INTEGER;
   DECLARE VARIABLE K INTEGER;
   DECLARE VARIABLE CRLF CHAR(2);
BEGIN
   CRLF=' ';
   FOR SELECT ID, GETRANDOM(9999999)
   FROM SELECT_RANDOM(:N) ORDER BY 2
   INTO :ID, :K
   DO
      INSERT INTO T_EXTERNAL(ID,CRLF) VALUES(:ID, :CRLF);
END ;

Таким образом, считаю, что тест поставлен более-менее корректно.

Имеются 3 тестируемые процедуры (в порядке поступления предложений):
  1. Мой вариант:
CREATE PROCEDURE TEST1
AS
   DECLARE VARIABLE ID INTEGER;
BEGIN
   FOR SELECT ID
   FROM T_EXTERNAL
   INTO :ID
   DO
      IF (EXISTS(SELECT ID FROM T_INTERNAL WHERE ID=:ID)) THEN
         UPDATE T_INTERNAL T SET T.TSTAMP=CURRENT_TIMESTAMP WHERE ID=:ID;
      ELSE
         INSERT INTO T_INTERNAL(ID) VALUES(:ID);
END;
  1. Вариант Дмитрия Попова (чуть-чуть изменённый):
CREATE PROCEDURE TEST2
AS
   DECLARE VARIABLE ID INTEGER;
   DECLARE VARIABLE K INTEGER;
BEGIN
   FOR SELECT ID
   FROM T_EXTERNAL
   INTO :ID
   DO
      BEGIN
         K=NULL;
         FOR SELECT ID FROM T_INTERNAL
         WHERE ID=:ID
         INTO :K
         AS CURSOR TMPCURSOR
         DO
            UPDATE T_INTERNAL T SET T.TSTAMP=CURRENT_TIMESTAMP
            WHERE CURRENT OF TMPCURSOR;
         IF (K IS NULL) THEN
            INSERT INTO T_INTERNAL(ID) VALUES(:ID);
      END
END;
  1. Вариант от Vova Aksionov (если я правильно понял суть):
CREATE PROCEDURE TEST3
AS
   DECLARE VARIABLE ID INTEGER;
BEGIN
   FOR SELECT ID FROM T_EXTERNAL INTO :ID DO
   BEGIN
      INSERT INTO T_INTERNAL(ID) VALUES(:ID);
      WHEN SQLCODE -803 DO
         UPDATE T_INTERNAL T SET T.TSTAMP=CURRENT_TIMESTAMP
         WHERE ID=:ID;
   END
END;
  1. Вариант от Sash* (с форума www.sql.ru) для Firebird 1.5 с поддержкой row_count
CREATE PROCEDURE TEST4
AS
DECLARE VARIABLE ID INTEGER;
begin
   FOR SELECT ID FROM T_EXTERNAL INTO :ID DO
      BEGIN
         UPDATE T_INTERNAL T SET T.TSTAMP=CURRENT_TIMESTAMP WHERE ID=:ID;
         if (row_count = 0) then
            INSERT INTO T_INTERNAL(ID) VALUES(:ID);
      END
END


Тестирование

Перед проведением тестов была наполнена данными T_INTERNAL. (100 000 записей). Потом сформированы 3 варианта T_EXTERNAL, каждый по 100 000 записей, с количеством совпадений ключей 30%, 50%, 77% соответсвенно. Был проведён бекап/рестор. Перед _каждым_ тестом база восстанавливалась из бекапа заново. На сервере количество буферов выставлено в 10 000. Размер страницы 4к. Сервер FB-1.0.0.338. Использовался коннект по TCP/IP в монопольном режиме. На сервере других приложений, кроме IB запущено не было.
 

Результаты

1. Для 30% совпадения
процедура | время | чтений | вставок | модификаций
----------+-------+--------+---------+------------
Test1     | 1:50  | 60 000 | 70 000  | 30 000
Test2     | 1:42  | 30 000 | 70 000  | 30 000
Test3     | 2:29  | 30 000 | 100 000 | 30 000
2. Для 50% совпадения
процедура | время | чтений  | вставок | модификаций
----------+-------+---------+---------+------------
Test1     | 1:57  | 100 000 | 50 000 | 50 000
Test2     | 1:40  | 50 000  | 50 000 | 50 000
Test3     | 3:25  | 50 000  | 100 000 | 50 000
1. Для 77% совпадения
процедура | время | чтений  | вставок | модификаций
----------+-------+---------+---------+------------
Test1     | 2:03  | 154 000 | 23 000  | 77 000
Test2     | 1:36  | 77 000  | 23 000  | 77 000
Test3     | 4:26  | 77 000  | 100 000 | 77 000

Процедура TEST4 показала практически идентичные результаты с TEST2 в тесте 77% совпадения.
 

Выводы

  • Самый быстрый способ – использование rdb$db_key (при пом. курсора), как это реализовано в SP "Test2". Он практически не выполняет лишних операций, что видно из таблиц.
  • Несколько медленнее мой способ ("Test1"), что неудивительно, т. к. каждая запись читается дважды.
  • Ну и самый медленный – с использованием exception.

Вариант 4 выглядит более элегантно и читаемо, эквивалентен по скорости лучшему варианту (TEST2), но может использоваться только в Firebird 1.5 и выше.

Обратите внимание на статистику – попыток вставки в Test3 всегда 100 000. Видимо обработка исключений на сервере "стОит дорого". Даже при избыточном чтении (154 000) у "Test1", эффективность её выше, чем "Test3".


Примечание

Скрипт для реализации теста – testiu.zip. Каждый может убедиться в результатах сам.

With best regards, Alex Cherednichenko.
 
Примечание KDV. При использовании подобных процедур или вообще массовых вставок или обновлений обратите внимание на результаты теста массового обновления записей. Если "пакет" записей в одной транзакции составляет примерно 100 тысяч, и при этом видно, что к концу обработки пакета скорость замедляется, рекомендую уменьшить пакет в 2 раза и попробовать еще раз. Если скорость вставки такого пакета будет более, чем в 2 раза выше, то стоит оставить размер пакета на этом уровне, и если увеличивать его, то осторожно, т. к. размер пакета, при котором начинается "торможение", зависит от размера вставляемых или обновляемых записей.

Вариант для Firebird 2.0

WildSery предложил вариант с использованием нового синтаксиса курсоров Firebird 2.0. Как утверждается, вариант работает быстрее всех изложенных в этой статье. Цветом помечены объявления курсоров и их использование в тексте процедуры.
create procedure test5
as
   declare e cursor for (select id from t_external order by cast(id as integer));  
   declare i cursor for (select id from t_internal order by id);  
   declare variable id_e integer;  
   declare variable prev_id_e integer = -1;  
   declare variable id_i integer = -1;  
   declare variable no_i_read integer = 0;
begin  
   open e; open i;  
   fetch e into id_e;  
   if (row_count = 0) then exit;  
   
   while (0=0) do begin    
      if (no_i_read = 0) then      
         while (id_i < id_e) do begin        
            fetch i into id_i;        
            if (row_count = 0) then begin          
               no_i_read = 1; leave;        
            end      
         end    
   if (id_i = id_e or id_e = prev_id_e) then      
      UPDATE T_INTERNAL SET TSTAMP=CURRENT_TIMESTAMP WHERE ID=:ID_E;    
   else      
      INSERT INTO T_INTERNAL(ID) VALUES(:ID_E);    
   prev_id_e = id_e;    
   fetch e into id_e;    
   if (row_count = 0) then exit;  
   end
end
 
Впервые опубликовано на www.ibase.ru.

Подпишитесь на новости Firebird в России

Подписаться