Alex Cherednichenko, 20.10.2001.
update: 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;
2) "перемешивает" данные сформированные из процедуры 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;
2) Вариант Дмитрия Попова (чуть-чуть изменённый):
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;
3) Вариант от 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;
4) Вариант от 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.
При использовании подобных процедур или вообще массовых вставок или обновлений обратите внимание на результаты теста массового обновления записей. Если "пакет" записей в одной транзакции составляет примерно 100 тысяч, и при этом видно что к концу обработки пакета скорость замедляется, рекомендую уменьшить пакет в 2 раза и попробовать еще раз. Если скорость вставки такого пакета будет более чем в 2 раза выше, то стоит оставить размер пакета на этом уровне, и если увеличивать его, то осторожно, т.к. размер пакета, при котором начинается "торможение", зависит от размера вставляемых или обновляемых записей.
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