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 процедуры:
- формирует 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;
- "перемешивает" данные, сформированные из процедуры 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 тестируемые процедуры (в порядке поступления предложений):
- Мой вариант:
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;
- Вариант Дмитрия Попова (чуть-чуть изменённый):
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;
- Вариант от 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;
- Вариант от 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.