Все технические форумы на одном сайте Удобный поиск информации с популярных форумов в одном месте
Вопрос: Ускорение insert в таблицу

Коллеги, проблема.
Есть процедура, которая циклом вставляет данные в таблицу MSSQL 2014 Developer. За одну итерацию 12-40к строк.
Всего процедура обрабатывает ~90 млн строк за 48 минут. В таблице для вставки нет индексов, у базы модель восстановления Simple.
В плане запроса ~80% ресурсов приходитcя на этот insert.

Уже делал:
1). Секционирование таблицы, в которую вставляются данные, по годам с параллельным запуском этой процедуры с разными диапазонами дат.
2). Перевод этой таблицы в InMemory.
Помогло слабо.
Что еще можно попробовать?
Ответ:
dvim
ondorsal,
Я бы шел в сторону bcp или параметров.
Быстрее этого в природе ничего нет.


А bcp может работать с таблицами?
Вопрос: Расщепление таблицы (Insert в две таблицы) или обработка предиката/антипредиката

Привет всем,

есть задача insert'нуть часть таблицы T1 (условием where) в таблицу T2, а остальную часть (от таблицы T1) insert'нуть в таблицу T3

По простому можно так:
insert into T2 select * from T1 where <предикат>;
insert into T3 select * from T1 where not <предикат>;


Вопрос в том, можно ли остальную часть не вытаскивать из таблицы T1 заново?

или для oracle, просмотрев в предыдущем запросе таблицу, повторно просмотреть с "антипредикатом" будет быстрее? (что-то вроде статистики (как при одинаковых запросах), интересует последовательно идущие insert'ы (в динамическом SQL) в пределах одной процедуры?)

в принципе устраивает вариант "вырезать"-переименовать
но delete не очень хочется использовать (скорость delete'ов на наших объемах значительно уступает insert'ам)

insert into T2 select * from T1 where <предикат>;
delete from from T1 where <предикат>;
rename T1 to T3;


на данный момент склоняюсь к варианту 2х insert'ов с использованием секций (по критерию предиката), но возможно уважаемое сообщество подскажет более оптимальный вариант

Заранее спасибо
Ответ:
ghazi
Правильно ли я полагаю что, имея секционированную таблицу по критерию предиката (возможно даже из 2х секций : предикат/антипредикат) insert'ы будут выполняться быстрее?


Ну да, а еще делиты и апдейты. Конечно, нет. Если у вас задача положить все книги на одну полку, то выполните ли вы это быстрее, если нужно будет положить их на две?
Вопрос: bulk-insert'ы при контроле поля на not-null: benchmark для 2 млн строк в 2.5 SC vs 3.0 SC

hi all.

Понадобилось как-то затащить в таблицу оч-чень много строк из базы-источника. DDL таблиц полностью совпадает, перенос идёт с помощью IB DataPump.
Ясен пень, что если убрать индексы с таблицы-приёмника, то скорость увеличится. Но захотелось также выкинуть (временно) все check'и на ней - "а вдруг взлетит еще быстрее ?".

Результат для 2.5 слегка удивил.
Если not-null задано вот так:
recreate table tgt1(id int not null); -- field-defined not-null constraint
или вот так:
create domain dm_id_check_nn int check(value is not null);
recreate table tgt4(id dm_id_check_nn); -- nullable domain + EXPLICIT constraint on domain
или еще вот так:
create domain dm_id_not_null int not null;
recreate table tgt5(id dm_id_not_null);
insert into tgt5 select * from src; -- NOT-null domain constraint
-- то скорость инсертов от наличия/отсутствия этих констрейнтов практически НЕ меняется.

Если же not-null обеспечивается в явном виде:
recreate table tgt2(id int, constraint tgt2_chk_id_nn check(id is not null));
insert into tgt2 select * from src; -- EXPLICIT not-null constraint on field
или вообще по-дэбильному:
recreate table tgt3(id int); create trigger tgt3_biu for tgt3 active before insert or update as begin if (new.id is null) then exception exc_id_nn; end
insert into tgt3 select * from src; -- trigger checking
-- то скорость инсертов от удаления этих перлов растёт (в 2.5) примерно в 3 раза.

Почему так сильно проигрывают предпоследний и последний варианты ? (вариант через триггер хотя и выглядит "не комильфо", однако все check'и ведь тоже внутрях реализованы как триггера! Поэтому столь большая разница как-то настораживает).

#######################

Результат для 3.0 также породил душевные терзания.
В нём явный проигрыш в скорости инсертов наблюдается там же, где и для 2.5. Однако статистика вставок проигрывает во всех остальных случаях проигрывает примерно в 1.5 раза. ДЕ мне как-то говорил, что 3.0 будет выигрывать у 2.5 только при многопользовательской нагрузке, а в моно-коннекте выигрыш совсем не гарантирован. Однако дифферент 1.5 раза - как-то уж очень сильно... :( Получается, миграцию действительно больших данных вообще лучше в 2.5 делать, а затем b/r ?

Вот тест:
+
-- init DDL:
recreate table src(id int);
commit;
set term ^;
execute block as
declare n int = 2000000;
begin
while (n>0) do insert into src(id) values(:n) returning :n-1 into n;
end
^
set term ;^
commit;

-- test:

set bail on;
recreate table tgt0(id int);
recreate table tgt1(id int not null);
recreate table tgt2(id int, constraint tgt2_chk_id_nn check(id is not null));
recreate table tgt3(id int);
recreate table tgt4(id int);
recreate table tgt5(id int);
commit;

set term ^;
execute block as
begin
begin
execute statement 'drop domain dm_id_check_nn';
when any do begin end
end
begin
execute statement 'drop domain dm_id_not_null';
when any do begin end
end
begin
execute statement 'drop exception exc_id_nn';
when any do begin end
end
end
^
set term ;^
commit;
create domain dm_id_check_nn int check(value is not null);
create domain dm_id_not_null int not null;
commit;

recreate table tgt4(id dm_id_check_nn);
recreate table tgt5(id dm_id_not_null);

create exception exc_id_nn 'id is null';
commit;

set term ^;
create trigger tgt3_biu for tgt3 active before insert or update as
begin
if (new.id is null) then exception exc_id_nn;
end
^
set term ;^
commit;

set stat on;
set count on;
set echo on;

insert into tgt0 select * from src; -- no conctraints
insert into tgt1 select * from src; -- field-defined constraint
insert into tgt2 select * from src; -- EXPLICIT not-null constraint on field
insert into tgt3 select * from src; -- trigger checking
insert into tgt4 select * from src; -- domain, EXPLICIT constraint
insert into tgt5 select * from src; -- domain-defined not-null constraint
set echo off;
set stat off;
commit;

А вот сводный результат по пяти запускам на каждом ФБ (приведены данные, начиная со второго прогона в каждом случае, дабы убрать влияние затрат на рост файла БД):

0.
recreate table tgt0(id int);
insert into tgt0 select * from src; -- no conctraints
run-1run-2run-3run-4run-5
elapsed_ms, FB 3.0 SC51075083508350835121
elapsed_ms, FB 2.5 SC32883297330332843286
ratio: 3.0 / 2.5:1,551,541,541,551,56


1.
recreate table tgt1(id int not null);
insert into tgt1 select * from src; -- field-defined not-null constraint
run-1run-2run-3run-4run-5
elapsed_ms, FB 3.0 SC53105303528653025307
elapsed_ms, FB 2.5 SC35493546353135133500
ratio: 3.0 / 2.5:1,501,501,501,511,52


2.
recreate table tgt2(id int, constraint tgt2_chk_id_nn check(id is not null));
insert into tgt2 select * from src; -- EXPLICIT not-null constraint on field
run-1run-2run-3run-4run-5
elapsed_ms, FB 3.0 SC74807474748574818199
elapsed_ms, FB 2.5 SC1010610125100911009510098
ratio: 3.0 / 2.5:0,740,740,740,740,81


3.
recreate table tgt3(id int); 
create trigger tgt3_biu for tgt3 ... as begin if (new.id is null) then exception exc_id_nn; end
insert into tgt3 select * from src; -- trigger checking
run-1run-2run-3run-4run-5
elapsed_ms, FB 3.0 SC89358907958989098981
elapsed_ms, FB 2.5 SC1194812103114961244911990
ratio: 3.0 / 2.5:0,750,740,830,720,75


4.
create domain dm_id_check_nn int check(value is not null);
recreate table tgt4(id dm_id_check_nn);
insert into tgt4 select * from src; -- domain, EXPLICIT constraint
run-1run-2run-3run-4run-5
elapsed_ms, FB 3.0 SC53625317531453105301
elapsed_ms, FB 2.5 SC35383518351635373523
ratio: 3.0 / 2.5:1,521,511,511,501,50


5.
create domain dm_id_not_null int not null;
recreate table tgt5(id dm_id_not_null);
insert into tgt5 select * from src; -- domain-defined not-null constraint
run-1run-2run-3run-4run-5
elapsed_ms, FB 3.0 SC55995329532853255323
elapsed_ms, FB 2.5 SC39983549350535193497
ratio: 3.0 / 2.5:1,401,501,521,511,52


PS.
Версии ФБ:
LI-V2.5.5.26910
LI-V3.0.0.32008

База в обоих случаях: page_size = 4K, fw = OFF.
Кол-во буферов в обоих инстансах ФБ: 512, арх-ра: SuperClassic.
Ответ:
Симонов Денис
в твоём тесте получается, что в трёшке триггеры работают быстрее, а движок медленнее. Странно это...
ну, вот так вот... прогони у себя, получишь, скорее всего тоже самое.

Симонов Денис
И ещё у тебя тест не совсем чистый. Где гарантия что медленней именно insert, а не select * from src
Сколько там хоть записей то и как они распределены?
затраты на select * from src - это по-любасу постоянная величина. И в стартовом посте всё видно, под спойлером: таблица содержит только числовое поле ID, от 1 до 2'000'000. Индексов нигде нету.
Вопрос: insert в связанные таблицы

Всем привет!
Имеется две таблицы.
Системные блоки:
CREATE TABLE sb (
    id      INTEGER NOT NULL
                    PRIMARY KEY AUTOINCREMENT
                    UNIQUE,
    sb_name TEXT,
    sn_type TEXT,
    sn      TEXT,
    serv    INTEGER,
    id_room INTEGER REFERENCES room (id) ON DELETE CASCADE
                                         ON UPDATE CASCADE,
    id_hard INTEGER REFERENCES hard (id) ON DELETE CASCADE
                                         ON UPDATE CASCADE,
    id_szi  INTEGER REFERENCES szi (id) ON DELETE CASCADE
                                        ON UPDATE CASCADE
);

Помещения:
CREATE TABLE room (
    id        INTEGER NOT NULL
                      PRIMARY KEY AUTOINCREMENT
                      UNIQUE,
    room_type TEXT,
    num       TEXT,
    floor     INTEGER
);


Они, соответственно, связаны по id_room. Также есть еще таблицы, связанные с "sb", но о них в данном случае не говорим.
Задача простая: вставить запись в обе таблицы, а именно вставить данные в следующие поля: sb.sb_name, room.room_type, room.num, room.floor.

Выполняю запрос:
INSERT INTO room (room_type, num, floor) VALUES ("Каб.", "5", "1");
INSERT INTO sb (sb_name, id_room) VALUES ("ИМЯ компьютера", last_insert_rowid());


На то вылезает ошибка:
Ошибка при выполнении SQL запроса к базе данных 'database': FOREIGN KEY constraint failed

В чем может быть проблема? :(

PS: все телодвижения выполняю в SQLiteStudio
Ответ:
G00dWINe
У меня тут возник следующий вопрос!

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

Допустим, мы добавляем записи этим запросом, но в одном помещении находится несколько компьютеров?

Если выполнить два раза эту sql-конструкцию, то в таблицу sb внесутся две записи, ссылающиеся на разные записи в room (которые в room будут одинаковыми, различия будут лишь в id).
Нет, тогда ты получишь фигню на втором компьютере.

INSERT INTO room (room_type, num, floor) VALUES ("Каб.", "5", "1");
INSERT INTO sb (sb_name, id_room) VALUES ("первый компьютер", last_insert_rowid()); -- last_insert_rowid() = room.id
INSERT INTO sb (sb_name, id_room) VALUES ("второй компьютер", last_insert_rowid()); -- last_insert_rowid() = sb.id

Так что, проще всего не используй эту функцию напрямую. Делай что-то в духе:
sql.execute ( 'INSERT INTO room (room_type, num, floor) VALUES ("Каб.", "5", "1")' )
set rs = sql.query ( 'SELECT last_insert_rowid()' )
room_id = rs.field(0)
sql.execute ( 'INSERT INTO sb (sb_name, id_room) VALUES ("первый компьютер",' + room_id +' )' )
sql.execute ( 'INSERT INTO sb (sb_name, id_room) VALUES ("второй компьютер",' + room_id +' )' )
Вопрос: insert if not exists

Всем доброго времени суток!

Имеется две связанные таблицы. И временная таблица tmp_foo, в которой хранится набор foo_name,bar_name.

Мне необходимо вставить в таблицу foo все значения из tmp_foo так, чтобы сначала была проверка, все ли bar_name из tmp_foo существуют в bar и если нет, то записала эти новые значения. А потом уже вставляла все значения (insert ... on duplicate key update) в таблицу foo.

В данном случае, необходимо:
  • добавить в таблицу bar значения 'd' и 'e' (insert if not exists?)
  • insert into foo (foo_id,bar_id) (select ... from tmp_foo join bar on duplicate key update ...)

    Со вторым пунктом вроде понятно, а вот первый не понимаю, как сделать.

    CREATE TABLE IF NOT EXISTS bar (
    bar_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    bar_name VARCHAR(255),
    PRIMARY KEY (bar_id),
    UNIQUE KEY bar_name (bar_name)
    ) ENGINE = INNODB;
    
    CREATE TABLE IF NOT EXISTS foo (
    foo_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    foo_name VARCHAR(255),
    bar_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (foo_id),
    UNIQUE KEY foo_name (foo_name),
    CONSTRAINT fk_bar_id FOREIGN KEY (bar_id) REFERENCES bar (bar_id)
    ) ENGINE = INNODB;
    
    INSERT INTO bar VALUES (1,'a'),(2,'b'),(3,'c');
    INSERT INTO foo VALUE (1,'qwe',1),(2,'asd',1),(3,'zxc',2),(4,'rty',2),(5,'dfg',3);
    
    SELECT f.foo_id,f.foo_name,b.bar_id,b.bar_name FROM foo f JOIN bar b ON f.bar_id=b.bar_id;
    
    +--------+----------+--------+----------+
    | foo_id | foo_name | bar_id | bar_name |
    +--------+----------+--------+----------+
    |      1 | qwe      |      1 | a        |
    |      2 | asd      |      1 | a        |
    |      3 | zxc      |      2 | b        |
    |      4 | rty      |      2 | b        |
    |      5 | dfg      |      3 | c        |
    +--------+----------+--------+----------+
    
    CREATE TEMPORARY TABLE IF NOT EXISTS tmp_foo (
    foo_name VARCHAR(255),
    bar_name VARCHAR(255)
    );
    
    INSERT INTO tmp_foo VALUES ('qwe','a'),('asd','b'),('zxc','b'),('rty','d'),('dfg','d'),('cvb','a'),('uio','e');
    
    SELECT * FROM tmp_foo;
    
    +----------+----------+
    | foo_name | bar_name |
    +----------+----------+
    | qwe      | a        |
    | asd      | b        |
    | zxc      | b        |
    | rty      | d        |
    | dfg      | d        |
    | cvb      | a        |
    | uio      | e        |
    +----------+----------+
    
  • Ответ: anvano,

    Точно. Спасибо большое!

    Получилось следующее:
    INSERT IGNORE INTO bar (bar_name) 
    SELECT bar_name FROM tmp_foo;
    
    INSERT IGNORE INTO foo (foo_name,bar_id)
    SELECT t.foo_name, b.bar_id FROM tmp_foo t JOIN bar b ON b.bar_name=t.bar_name
    ON DUPLICATE KEY UPDATE bar_id=b.bar_id;
    
    SELECT f.foo_id,f.foo_name,b.bar_id,b.bar_name FROM foo f JOIN bar b ON f.bar_id=b.bar_id;
    
    +--------+----------+--------+----------+
    | foo_id | foo_name | bar_id | bar_name |
    +--------+----------+--------+----------+
    |      1 | qwe      |      1 | a        |
    |      6 | cvb      |      1 | a        |
    |      2 | asd      |      2 | b        |
    |      3 | zxc      |      2 | b        |
    |      4 | rty      |      4 | d        |
    |      5 | dfg      |      4 | d        |
    |      7 | uio      |      5 | e        |
    +--------+----------+--------+----------+
    
    Вопрос: Блокируется таблица InnoDB при INSERT или UPDATE

    Всем привет. Сразу скажу, что очень не опытен в базах данных, поэтому прошу объяснить что делать в моей ситуации.
    Прочитал, что таблицы InnoDB блокируется на уровне строк, а не всей таблицы. Поэтому работаю именно с InnoDB.
    Есть некая таблица со столбцами ID и NAME
    В этой таблице уже есть 1000 записей, у которых NAME = Вася
    Далее я одним PHP-скриптом генерирую 100.000 вставок новых строк в эту таблицу, т.е. INSERT, при этом ID автоматический, а NAME = Петя
    Пока идёт эта вставка я другим скриптом делаю выборку 10 записей, т.е. SELECT при этом NAME = Вася
    Но почему-то выборка НЕ идёт до тех пор, пока не закончатся INSERTы
    ---
    Тоже самое происходит когда делаю UPDATE того же количества записей, при этом апдейт идёт по ID (я намеренно затрагиваю те ID, которые не имеют отношения к NAME = Вася) и снова нет возможности прочитать строки с NAME = Вася пока не закончатся апдейты.
    -----------------------
    Очень прошу подсказать что я делаю не так и почему блокируется вся таблица при INSERT или UPDATE, и если можно простым доступным языком.
    Ответ:
    Melkij
    Ну так вот, session_start до явного или неявного (при завершении скрипта) вызова session_write_close блокирует выполнение параллельных запросов с этому же session id.

    Спасибо за разъяснения...
    Я сейчас любопытства ради создал аналогичную таблицу, но уже MyISAM
    Проделал все те операции разумеется с разных браузеров и почему-то всё работает также хорошо как и InnoDB, т.е. происходит чтение строк тех, которые успели добавится на момент селекта. И никакой блокировки, ни какого ожидания ни при INSERT ни при UPDATE. Даже получается сами INSERT и UPDATE в MyISAM выполняются в 4 раза быстрее. Тогда не могу понять в чём суть блокировки таблицы если разницы по факту нет? Что-то я запутался...
    Вопрос: Прерывание операции Insert триггером. Как сделать?

    Мне нужно, чтобы при невыполнении некоего условия строка в таблицу просто не вставлялась.
    Написал такой триггерок:

    CREATE OR REPLACE TRIGGER GAIDEV.Can_Insert_nomer_98
    BEFORE INSERT
    ON GAIDEV.AMT_REPLICATION 
    REFERENCING NEW AS New OLD AS Old
    FOR EACH ROW
    DECLARE
        NotInAmt_Auto Exception;
        lv_Cnt  Number :=0;
    BEGIN
        if :new.Ident='nomer_98' then
            select count(*)
            into lv_Cnt
            from amt_auto
            where
                ident=:new.Ident and
                (:new.Nomer between Nom_Beg and Nom_End);            
            if lv_Cnt = 0 then 
                Raise NotInAmt_Auto; 
            end if;
        end if;
    END Can_Insert_nomer_98;
    


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

    Возможно, затруднение моё чисто дилетантское, но прошу подсказки у уважаемых знатоков.
    Ответ: Можно
    1) создать вьюху для таблицы
    2) для нее написать триггер instead of insert
    3) в триггере проверять условие, если запись не нужно вставлять, просто ничего не делать, иначе делать insert в таблицу.

    Но тогда и вставки должны идти во вьюху, а не таблицу.

    -- молча не вставлять числа кратные 3
    create table ttt (ccc number); 
    create view vvv as select ccc from ttt;
    
    create or replace trigger trg
    instead of insert 
    on vvv
    for each row
    begin
      if mod (:new.ccc, 3) = 0 then
        null;
       else
         insert into ttt (ccc) values (:new.ccc);
       end if;
    end;
    
    insert into vvv (ccc) select rownum from dual connect by level <= 10; 
    


    Еще можно попробывать написать compound-триггер, запоминающий ИД записи и удаляющий ее после вставки. В этой ветке
    [url=][/url] есть пример, но он не будет работать, если нужно удалить таким образом более одной записи.
    Вопрос: Вопрос по INSERT INTO ... SELECT ... RETURNING

    Столкнулся с проблемой, есть следующая структура данных для хранения информации о заказах:

    -- Собственно таблица заказов
    CREATE TABLE site_order (
      id bigint NOT NULL DEFAULT nextval('id'::regclass),
      status integer,
      phone text,
      CONSTRAINT site_order_pk PRIMARY KEY (id)
    );
    -- Таблица состава заказа
    CREATE TABLE site_order_item (
      id bigint NOT NULL DEFAULT nextval('id'::regclass),
      order_id bigint NOT NULL,
      name text,
      amount integer,
      cost real,
      CONSTRAINT site_order_item_pk PRIMARY KEY (id),
      CONSTRAINT site_order_item_fk_order FOREIGN KEY (order_id) REFERENCES site_order (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE
    );
    -- Таблица примечаний к составу заказа
    CREATE TABLE site_order_item_detail
    (
      id bigint NOT NULL DEFAULT nextval('id'::regclass),
      order_item_id bigint NOT NULL,
      detail text,
      CONSTRAINT site_order_item_detail_pk PRIMARY KEY (id),
      CONSTRAINT site_order_item_detail_fk_item FOREIGN KEY (order_item_id) REFERENCES site_order_item (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE
    );
    


    Есть данные:

    WITH o AS (
      INSERT INTO site_order (id,status,phone) VALUES (42,2,'+7 (000) 000-00-00')
      RETURNING *
    ),
    item_list (name,amount,cost) AS (VALUES ('name 1', 1, 100), ('name 2', 1, 200), ('name 1', 3, 100)),
    item AS (
      INSERT INTO site_order_item (order_id,name,amount,cost)
      SELECT o.id, v.name, v.amount, v.cost FROM o, item_list as v
      RETURNING *
    ),
    detail_list (detail) AS (VALUES ('detail 1'), ('detail 2')),
    detail AS (
      INSERT INTO site_order_item_detail (order_item_id, detail)
      SELECT oi.id, v.detail FROM (SELECT * FROM item ORDER BY random() LIMIT 1) as oi, detail_list as v
      RETURNING *
    ) SELECT * from o;
    


    Хочется странного - сделать копию заказа вот таким образом:

    WITH o AS (
      INSERT INTO site_order (phone,status)
      SELECT phone,0 FROM site_order WHERE id=42 AND status=2
      RETURNING *
    ), item AS (
      INSERT INTO site_order_item (order_id,name,amount,cost)
      SELECT o.id,oi.name, oi.amount,oi.cost
      FROM o, site_order_item AS oi
      WHERE oi.order_id=42
      RETURNING *, o.id AS o_item_id  -- Это не работает, хотя хотелось-бы
    ), detail AS (
      INSERT INTO site_order_item_detail (order_item_id,detail)
      SELECT item.id, d.detil
      FROM item
      INNER JOIN site_order_item_detail AS d ON (d.order_item_id = item.o_item_id)
      RETURNING *  
    ) select * from o;
    


    В теории можно добавить поле parent_id в теблицу site_order_item и в него заносить старые значения, но быть может можно обойтись без этого
    Ответ: crause,

    Нет, реальная таблица получается сложнее, это для примера сделано упрощение.
    Вопрос: INSERT OR REPLACE + trigger

    Привет.

    Кто знает почему тут срабатывает 4 раза триггер на Insert?
    ведь при последующих INSERT OR REPLACE insert-а не происходит
    по идее должно быть или один Insert + 3 Update
    или insert + последовательности delete+insert

    Это баг?

    CREATE TABLE IF NOT EXISTS userinfo (nick VARCHAR(64) NOT NULL,last_updated DATETIME NOT NULL,ip_address VARCHAR(39) NOT NULL,share VARCHAR(24) NOT NULL,description VARCHAR(192),tag VARCHAR(192),connection VARCHAR(32),email VARC
    AR(96),UNIQUE (nick COLLATE NOCASE));
    CREATE TABLE IF NOT EXISTS userinfo_log(time_operation DATETIME, operation VARCHAR(1));
    CREATE TRIGGER tr_u_userinfo BEFORE UPDATE ON userinfo FOR EACH ROW
    BEGIN
       INSERT into userinfo_log(time_operation, operation) VALUES(DATETIME('now'),'U');
    END;
    CREATE TRIGGER tr_d_userinfo BEFORE DELETE ON userinfo FOR EACH ROW
    BEGIN
       INSERT into userinfo_log(time_operation, operation) VALUES(DATETIME('now'),'D');
    END;
    CREATE TRIGGER tr_i_userinfo BEFORE INSERT ON userinfo FOR EACH ROW
    BEGIN
       INSERT into userinfo_log(time_operation, operation) VALUES(DATETIME('now'),'I');
    END;
    INSERT OR REPLACE INTO userinfo (nick, last_updated, ip_address, share, description, tag, connection, email) VALUES('nick1',DATETIME('now'),'ip','share','description','tag','connection','email');
    INSERT OR REPLACE INTO userinfo (nick, last_updated, ip_address, share, description, tag, connection, email) VALUES('nick1',DATETIME('now'),'ip','share','description','tag','connection','email');
    INSERT OR REPLACE INTO userinfo (nick, last_updated, ip_address, share, description, tag, connection, email) VALUES('nick1',DATETIME('now'),'ip','share','description','tag','connection','email');
    INSERT OR REPLACE INTO userinfo (nick, last_updated, ip_address, share, description, tag, connection, email) VALUES('nick1',DATETIME('now'),'ip','share','description','tag','connection','email');
    select * from userinfo_log;
    2016-01-09 18:00:04|I
    2016-01-09 18:00:04|I
    2016-01-09 18:00:05|I
    2016-01-09 18:00:05|I
    select * from userinfo;
    nick1|2016-01-09 18:00:05|ip|share|description|tag|connection|email
    update userinfo set ip_address = 'x';
    select * from userinfo_log;
    2016-01-09 18:00:04|I
    2016-01-09 18:00:04|I
    2016-01-09 18:00:05|I
    2016-01-09 18:00:05|I
    2016-01-09 18:00:05|U
    delete from userinfo;
    select * from userinfo_log;
    2016-01-09 18:00:04|I
    2016-01-09 18:00:04|I
    2016-01-09 18:00:05|I
    2016-01-09 18:00:05|I
    2016-01-09 18:00:05|U
    2016-01-09 18:00:05|D
    
    Ответ: Провел тест на таблице из 150 тыс записей.
    INSERT OR REPLACE INTO userinfo - 15 сек
    UPDATE userinfo set - 10 сек.

    Код теста
    				for (int j = 0; j < 2; ++j)
    				{
    					{
    						sqlite3_connection l_DB;
    						File::deleteFile("users.sqlite");
    						File::copyFile("users-orig.sqlite", "users.sqlite");
    						l_DB.open("users.sqlite");
    						auto_ptr<sqlite3_command> l_load_all(new sqlite3_command(l_DB, "select nick from userinfo"));
    						sqlite3_reader l_q = l_load_all.get()->executereader();
    						std::vector<string> l_nick;
    						l_nick.reserve(160000);
    						while (l_q.read())
    						{
    							l_nick.push_back(l_q.getstring(0));
    						}
    						CFlySQLCommand l_sql;
    						sqlite3_transaction l_trans(l_DB);
    						{
    							CFlyLockProfiler l_log;
    							for (auto i = l_nick.cbegin(); i != l_nick.cend(); ++i)
    							{
    								switch (j)
    								{
    									case 1:
    										l_sql.init(l_DB,
    										           "INSERT OR REPLACE INTO userinfo (nick, last_updated, ip_address, share, description, tag, connection, email) VALUES(?,DATETIME('now'),'ip','share','description','tag','connection','email')");
    										break;
    									case 0:
    										l_sql.init(l_DB,
    										           "UPDATE userinfo set last_updated = DATETIME('now'), ip_address == 'ip', share = 'share', description = 'description', tag = 'tag', connection = 'connection', email = 'email' where nick = ?");
    										break;
    								}
    								l_sql->bind(1, *i, SQLITE_STATIC);
    								l_sql->executenonquery();
    							}
    							l_trans.commit();
    							l_log.log("D:\\time.txt", j);
    						}
    					}
    				}
    


    К сожалению получается, если запись в таблице существует использовать INSERT OR REPLACE не эффективно.
    пока заменил у себя критичные места на такой код

    1. делаю update
    2. считаю sqlite3_changes
    3. если sqlite3_changes = 0 зову insert or replace
    replace - костыль для защиты от вставки такой записи другим процессом

    			m_update_last_ip.init(m_flySQLiteDB,
    			                      "update user_db.user_info set last_ip=? where dic_hub=? and nick=?");
    			m_update_last_ip->bind(1, __int64(p_last_ip.to_ulong()));
    			m_update_last_ip->bind(2, __int64(p_hub_id));
    			m_update_last_ip->bind(3, p_nick, SQLITE_STATIC);
    			m_update_last_ip->executenonquery();
    			if (m_update_last_ip.sqlite3_changes() == 0)
    			{
    				m_insert_last_ip.init(m_flySQLiteDB,
    				                      "insert or replace into user_db.user_info(nick,dic_hub,last_ip) values(?,?,?)");
    				m_insert_last_ip->bind(1, p_nick, SQLITE_STATIC);
    				m_insert_last_ip->bind(2, __int64(p_hub_id));
    				m_insert_last_ip->bind(3, __int64(p_last_ip.to_ulong()));
    				m_insert_last_ip->executenonquery();
    			}
    


    У кого будут другие идеи - как сделать красивше?

    В идеале очень хотелось-бы получить от sqlite нативную команду "INSERT OR UPDATE" или "MERGE"
    которая не делает этот лишний Delete и всю модификацию выполняет атомарно
    без побочных эффектов в много поточной среде...
    Вопрос: Триггер на insert, update

    Добрый день.
    Для таблицы реализован триггер на операции insert, update.
    Смысл триггера следующий, берем все записи из таблицы inserted группируем их и вставляем в другую таблицу с определенными полями, в случае если совпадение есть и доп условие отрабатывает, тогда выполняем апдейт.
    Но столкнулся с проблемой, что не всегда все записи "инсертятся" в другую таблицу.
    Данные импортируются в таблицу как через джоб, где реализовано большое количество степов, так и вручную.
    Причем если были пропуски и запустить операцию еще раз по пропущенным записям все пройдет успешно.

    Текст триггера:

    if @@rowcount = 0 return;
    set nocount on;
    	
    merge tblArchivedDebts_OSD trg
    using (select i.DebtDate, i.Cust_id, getDate() Dlm, 0 IsCopied 
             from inserted i 
    	group by i.DebtDate, i.Cust_id) src on src.Cust_id = trg.Cust_id and src.DebtDate = trg.DebtDate
    when matched and trg.IsCopied = 1 then
        update
        set trg.IsCopied = 0
           , trg.SyncDate = src.Dlm
    when not matched then
        insert (Cust_id, DebtDate, SyncDate, IsCopied)
        values (src.Cust_id, src.DebtDate, src.Dlm, src.IsCopied);
    
    Ответ:
    Заинтересовал
    а то у меня не выходит
    instead of ни при чем. Различие будет при merge.
    use tempdb;
    go
    
    create table dbo.t (id int primary key, v int);
    insert into dbo.t values (1, 1);
    go
    
    create trigger dbo.tr_t
    on dbo.t
    after insert, update
    as
    begin
     select @@rowcount as [@@rowcount], count(*) as [count(*) from inserted] from inserted;
    end;
    go
    
    merge into dbo.t
    using (values (2, 2)) s(id, v) on s.id = t.id
    when matched then
     update
      set v = s.v
    when not matched by source then
     delete;
    go
    
    drop table dbo.t;
    go