Получение различий в записях между 2-мя почти идентичными таблицами

У меня есть процесс, который объединяет 40+ идентично структурированных баз данных в одну консолидированную базу данных, с той лишь разницей, что консолидированная база данных добавляет поле project_id в каждую таблицу.

Чтобы быть максимально эффективным, я стараюсь копировать / обновлять запись из исходных баз данных в консолидированную базу данных только в том случае, если она была добавлена ​​/ изменена. Я удаляю устаревшие записи из консолидированной базы данных, а затем копирую все несуществующие записи. Чтобы удалить устаревшие / измененные записи, я использую запрос, похожий на этот:

DELETE FROM <table> 
 WHERE NOT EXISTS (SELECT <primary keys> 
                     FROM <source> b 
                    WHERE ((<b.fields = a.fields>) or 
                          (b.fields is null and a.fields is null))) 
  AND PROJECT_ID = <project_id>

По большей части это работает, но одна из таблиц в исходной базе данных содержит более 700 000 записей, и выполнение этого запроса занимает более часа.

Как сделать этот запрос более эффективным?


sql performance not-exists
person aasukisuki    schedule 05.10.2009    source источник
comment
У вас есть указатель по полям?   -  person Eric J.    schedule 05.10.2009

Ответы (4)


arrow_upward
2
arrow_downward

Используйте метки времени или, что еще лучше, таблицы аудита, чтобы идентифицировать записи, которые изменились с момента «X», а затем сохраните время «X», когда началась последняя синхронизация. Мы используем это для каналов интерфейса.

person DVK    schedule 05.10.2009
comment
Это то, что вы добавили в базу данных / таблицы? IE: добавлено поле last_updated в каждую таблицу и audit_table в каждую базу данных? К сожалению, я не могу изменить схему исходных баз данных, поскольку они поступают от продукта поставщика. - person aasukisuki; 05.10.2009
comment
Технически я мог бы добавить таблицу аудита или даже поле к каждой таблице, которое будет использоваться в качестве метки времени, но процесс поставщика никогда не будет ничего добавлять в таблицу аудита или заполнять поле метки времени при изменении. - person aasukisuki; 05.10.2009
comment
Есть ли в таблице неубывающее поле? (Идентификатор сгенерированного БД или естественного происхождения)? В противном случае возможно заполнение таблицы аудита из периодически запущенного SP, но чертовски противно. - person DVK; 06.10.2009
comment
Также посмотрите, сможете ли вы каким-то образом подключиться к процессу поставщика неофициально. Например. может быть, от него в какой-то файловой системе или в строках списка БД остался какой-то осадок, который только что (или с 5 минут назад) был обработан. - person DVK; 06.10.2009

arrow_upward
0
arrow_downward

Вы можете попробовать LEFT JOIN с фильтром NULL:

DELETE      <table> 
FROM        <table> t
LEFT JOIN   <source> b 
        ON (t.Field1 = b.Field1 OR (t.Field1 IS NULL AND b.Field1 IS NULL))
        AND(t.Field2 = b.Field2 OR (t.Field2 IS NULL AND b.Field2 IS NULL))
        --//...
WHERE       t.PROJECT_ID = <project_id>
        AND b.PrimaryKey IS NULL --// any of the PK fields will do, but I really hope you do not use composite PKs

Но если вы сравниваете все столбцы, отличные от PK, ваш запрос пострадает.

В этом случае лучше добавить поле UpdatedAt TIMESTAMP (как предлагает DVK) в обе базы данных, которые вы можете обновить с помощью триггера AFTER UPDATE, тогда ваша процедура синхронизации будет намного быстрее, учитывая, что вы создаете индекс, включающий PK и столбец UpdatedAt .

person van    schedule 05.10.2009

arrow_upward
0
arrow_downward

Вы можете изменить порядок оператора WHERE; у него есть четыре сравнения, сначала поставьте то, которое, скорее всего, потерпит неудачу.

Если вы можете немного изменить базы данных / приложение, и вам нужно будет сделать это снова, битовое поле с надписью «обновлено» может быть неплохим дополнением.

person Dean J    schedule 05.10.2009

arrow_upward
0
arrow_downward

Я обычно переписываю такие запросы, чтобы избежать not ... Not In ужасно сказывается на производительности, хотя Not Exists улучшает это.

Прочтите эту статью, http://www.sql-server-pro.com/sql-where-clause-optimization.html

Мое предложение...

Выделите свой столбец pkey в рабочую / временную таблицу, добавьте столбец (флаг) int default 0 not null и проиндексируйте столбец pkey. Отметьте flag = 1, если в вашем подзапросе есть запись (намного быстрее!). Замените свой дополнительный выбор в основном запросе существующим где (выберите pkey из temptable, где flag = 0)

В результате получается возможность создать список «несуществующих» значений, которые можно использовать включительно из всеобъемлющего набора.

Вот наш общий набор. {1,2,3,4,5}

Вот существующий набор {1,3,4}

Мы создаем нашу рабочую таблицу из этих двух наборов (технически левое внешнее соединение) (запись: существует)

{1:1, 2:0, 3:1, 4:1, 5:0}

Наш набор "несуществующих записей"

{2,5} (выберите *, где flag = 0)

Наш продукт ... и намного быстрее (индексы!)

{1,2,3,4,5} in {2,5} = {2,5}

{1,2,3,4,5} не в {1,3,4} = {2,5}

Это можно сделать без рабочего стола, но его использование упрощает визуализацию происходящего.

Крис

person KSimons    schedule 05.10.2009