Какой шаблон SQL быстрее, чтобы избежать вставки повторяющихся строк?

Я знаю два способа вставки без дублирования. Первый использует предложение WHERE NOT EXISTS:

INSERT INTO table_name (col1, col2, col3)
SELECT %s, %s, %s
WHERE NOT EXISTS (
    SELECT * FROM table_name AS T
    WHERE T.col1 = %s
      AND T.col2 = %s)

другой делает LEFT JOIN:

INSERT INTO table_name (col1, col2, col3)
SELECT %s, %s, %s
FROM ( SELECT %s, %s, %s ) A
LEFT JOIN table_name B
ON  B.COL1 = %s
AND B.COL2 = %s
WHERE B.id IS NULL
LIMIT 1

Существует ли общее правило, согласно которому одно должно быть быстрее другого, или это зависит от таблиц? Есть ли другой способ, который лучше, чем оба?


postgresql sql database query-optimization
person Claudiu    schedule 25.06.2010    source источник
comment
Вы уверены, что хотите использовать SELECT * в первом примере? Выделить все столбцы? Как правило, так делать нельзя, но я не эксперт по Postgres.   -  person DOK    schedule 25.06.2010
comment
Можете ли вы выполнить оба запроса с помощью EXPLAIN и опубликовать это? Инструменты анализа помогут ответить на эти вопросы в каждом конкретном случае.   -  person Freiheit    schedule 25.06.2010

Ответы (2)


arrow_upward
5
arrow_downward

Я бы рекомендовал определить ограничение UNIQUE для столбцов, которые вам нужны, чтобы быть уникальными (в данном случае col1 и col2), а затем просто выполнить INSERT. Обрабатывайте исключения по мере необходимости.

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

Видеть:

person Bill Karwin    schedule 25.06.2010
comment
у меня уже есть уникальные ограничения. однако, когда они нарушаются, я должен либо зафиксировать, либо откатить всю транзакцию до сих пор. каждая моя транзакция требует нескольких вставок, и я не хочу частично фиксировать некоторые из них. есть ли способ лучше обрабатывать исключения, чтобы не испортить курсор/транзакцию? (делая это из Python, с помощью sqlobject или psycopg2) - person Claudiu; 25.06.2010
comment
Да: перехватить исключение. Если это нарушение повторяющегося ключа, вы можете игнорировать его, потому что это означает, что ваша строка уже существует. Если это другой тип исключения, у вас все равно возникла бы эта проблема (например, синтаксическая ошибка SQL, переполнение диска, разрыв сетевого подключения). - person Bill Karwin; 25.06.2010
comment
если я поймаю исключение, то попытаюсь выполнить другой запрос с тем же курсором, я получу psycopg2.InternalError: current transaction aborted; ignoring actions until end of transaction block или что-то в этом роде - person Claudiu; 25.06.2010
comment
Вот точное сообщение об ошибке: psycopg2.InternalError: current transaction is aborted, commands ignored until end of transaction block. что я могу сделать, чтобы предотвратить это состояние после того, как не удалось вставить таблицу? - person Claudiu; 25.06.2010
comment
я попытался задать вопрос более красноречиво здесь: stackoverflow.com/questions/3120688/ - person Claudiu; 25.06.2010

arrow_upward
-1
arrow_downward

Я думаю, что использование EXISTS более эффективно! Вы можете сделать так:

if exists(select 1 from table_name where col1 = %s and col2 = %s) then
  insert into table_name (col1, col2, col3)
  select %s, %s, %s;
end if;

при тестировании использование EXISTS примерно в 50 раз быстрее, чем использование NOT EXISTS.

другой метод использует EXCEPT.

INSERT INTO table_name (col1, col2, col3)
SELECT %s, %s, %s
except
select col1, col2, col3 from table_name

при тестировании использование EXCEPT примерно в 3 раза быстрее, чем использование NOT EXISTS.

person tinychen    schedule 27.07.2010