Лучшие методы сравнения таблиц значений

Пусть имеется две таблицы значений, имеющих одинаковый состав и типы колонок. Требуется сравнить эти таблицы с целью определения различий, имеющихся между ними.

Домысливая условия задачи самыми распространенными обстоятельствами, дополнительно установим, что:

1. Разный порядок одних и тех же строк в двух таблицах не делает таблицы различными (в задачах, где порядок строк существенен, всегда можно добавить колонку с номером строки, чтобы заметить их перестановку);
2. В одной таблице не может быть двух одинаковых строк (а если такое есть, то всегда можно произвести свертку по всем колонкам с подсчетом одинаковых строк в добавленной колонке – это упростит интерпретацию результатов сравнения).
3. Таблицы сравниваются путем непосредственного сравнения значений их элементов или ссылок. Если элементы таблиц содержат коллекции, то сравниваются только ссылки на коллекции без попыток определить равенство их содержания.

Второе уточнение автоматически приводит к тому, что в таблице всегда будут одна или более колонок, значение (комбинация значений) в которых будут уникальными и могут служить идентификатором строки. Такую колонку (набор колонок) можно называть ключом: простым в случае одной колонки или составным в более сложном случае. А еще лучше, по аналогии с регистрами, упомянутые колонки называть измерениями таблицы, а оставшиеся – ресурсами.

Выделение колонок-измерений позволяет при сравнении таблиц установить не только факт удаления или добавления строки, но и факт изменения строки, если в том же наборе измерений изменились ресурсы.

Например, при сравнении таблиц значений, полученных по оборотно-сальдовой ведомости счета учета сырья и материалов, измерениями будут колонки, содержащие номенклатуру и склад, а ресурсами – остатки и обороты счета. А при сравнении табличных частей «Товары» измерениями будут номенклатура, характеристика и серия, а ресурсами – все остальные реквизиты этой табличной части. И тогда путем сравнения версий табличных частей можно будет сказать, что такая-то номенклатура была удалена или добавлена, а такая-то – изменена.

При постановке задачи также определим форму представления результатов сравнения. Это наиболее уязвимое для критики решение. Поскольку от него зависит результат соревнования методов. Одна форма может быть удобной для одного метода, вторая для другого, третья для третьего, а практика в силу разнообразия задач и ситуаций ответу не помогает.

После долгих колебаний было принято следующее решение: результатом сравнения двух таблиц Таблица0 и Таблица1 должна быть таблица «Разница» той же структуры, что и сравниваемые таблицы. «Разница» должна содержать отличающиеся строки двух таблиц (удаленные, добавленные, измененные). При этом в дополнительном столбце «Знак» должна стоять отметка: 0 – если строка имеется в Таблице0 и 1 – если строка имеется в Таблице1. Это можно интерпретировать как 0 – строка удалена, 1 – добавлена, или 0 – строка до изменения, 1 – после. Кроме того (внимание!), строки с одинаковыми значениями измерений должны быть расположены друг под другом, что реализует удобный для визуального контроля способ «связывания» строк до и после изменения.

Например, если сравнить предлагаемым способом таблицу "7 класс" с таблицей "8 класс", то должна получиться таблица "Разница".

7 класс 8 класс Разница Предмет Оценка Предмет Оценка Предмет Оценка Знак Пение 5 Литература 5 Пение 5 0 Литература 5 Алгебра 4 Алгебра 5 0 Алгебра 5 Физика 5 Алгебра 4 1 Физика 5 Химия 4 Химия 4 1

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

Главным критерием оценки естественно выбрать время выполнения сравнения. Дополнительным критерием может служить простота функции сравнения. Время выполнения сравнения можно замерить специально созданной для этого обработкой. Простоту функций предлагается оценивать субъективно.

Обработка, с озданная для испытаний, генерирует таблицу значений с заданным числом строк и столбцов и заданным количеством измерений. Тип данных элементов выбирается из ограниченного списка примитивных типов: строка, число и дата, также можно задать длину значения. Значения элементов таблицы формируются случайным образом. Путем изменения первой таблицы формируется вторая. Количество изменений задается в процентном отношении к числу строк первой таблицы тремя различными показателями: процент удалений, изменений и добавлений. Также задается число повторений для определения среднего времени работы метода. Все тестируемые методы запускаются один за другим на одних и тех же тестовых таблицах. Используемая при тестировании обработка прикреплена к данной публикации, чтобы результаты можно было перепроверить на другом оборудовании и в другом программном окружении.

Всего для детального тестирования было отобрано семь различных методов:

Суть метода заключается в объединении таблиц путем дописывания в цикле по одной строке из первой таблицы ко второй. Затем делается добавление дополнительного столбца "Счёт" для последующего подсчета одинаковых строк. Подсчет делается сверткой по всем столбцам. Так определяются одинаковые и разные строки в первой и второй таблице. Те строки, которые встретились в объединенной таблице по одной, переписываются в таблицу разниц, которая затем сортируется по измерениям, чтобы строки до и после изменений оказались рядом. Вот код данной функции

Данная функция является небольшой модификацией предыдущей функции за счет того, что дописывание первой таблицы ко второй идет не по строкам, а по столбцам. Это в определенном диапазоне условий ускоряет операцию объединения таблиц

Данная функция построена на простой и ясной идее. В цикле перебираются строки первой таблицы. Для каждой строки делается попытка найти строку во второй таблице, соответствующую ей по значению измерений, с помощью метода "НайтиСтроки". Ресурсы найденных строк затем сравниваются на предмет наличия расхождений, найденная строка во второй таблице помечается нулем, чтобы затем отобрать непомеченные "единичные" строки как отсутствующие в первой таблице. Чтобы метод НайтиСтроки работал быстро, для второй таблицы создается один индекс по всей совокупности измерений.

Данная функция алгоритмически повторяет предыдущую, за исключением того, что вместо обычного индекса используется "самодельный" индекс на основе соответствия. Для этого вторая таблица предварительно обходится, в результате чего ссылки на ее строки запоминаются в дереве поиска, построенном на основе соответствия

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

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

Эта функция построена ровно на той же идее, что и функция 3.1, только реализована внутри запроса

Все приведенные здесь функции были достаточно тщательно оттюнингованы для достижения максимального быстродействия. С учетом опыта, полученного в ходе совместного тюнинга на форуме функций для одномерного случая. Однако это было сделано не так тщательно как там, поэтому, возможно, из некоторых функций можно выжать еще немного быстродействия.

Исследуем зависимость времени сравнения от числа строк в таблицах. Для этого используем следующие значения параметров тестирования. Число строк - 20000, 40000, 60000, 80000, 100000, число колонок - 10, число ключевых колонок - 1, тип данных - строка, длина строки - 10, процент удалений, изменений, добавлений - 5, число повторов теста - 2. Получим следующую зависимость, которую удобнее представить в виде графика.

Эта зависимость для большинства методов практически линейна! Так и должно быть. Время работы метода НайтиСтроки при наличии индекса не зависит от числа строк, поэтому соединение по индексу выполняется за линейное время. То же самое при использовании соответствия и слияния. При полном соединении в запросе для соединения таблиц равного размера скорее всего используется хэш-матч.

Нелинейность времени сортировки относительно небольшого количества отличающихся строк чуть-чуть отклоняет от прямой зависимость для свертки. Хуже дела у метода с использованием объединения копированием колонок - именно этот способ копирования вносит существенную нелинейность вдобавок к небольшой нелинейности сортировки. Из-за этого выгода применения "трюка" объединения таблиц на числе строк более 60000 теряется.

Теперь исследуем зависимость времени от длины значений типа строка. Число строк положим равным 50000. Остальные параметры такие же, как в 4.1. Результат представим в виде столбиковой диаграммы. Она лучше показывает соотношение времени работы разных методов и позволяет выделить лидера, которым в большинстве случаев яыляется метод свертки.

Видно, что зависимость времени от метода при изменении длины строки практически не меняется. Растет только время выполнения запросов.

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

Следующий интересный вопрос - отношение методов к типам данных. Его показывает следующая диаграмма. Здесь также число строк 50000, длина строкового и числового значения - 10. Остальное как в 4.1.

Из нее видно, что типданных сильнее всего сказывается на времени запросных методов. Для чисел лучше подходит группировка. И очень хорошо запросами обрабатываются даты.

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

Видно, что число колонок не сильно меняет сравнительную скорость методов. В наибольшей степени увеличение числа колонок замедляет работу запросов.

Более интересна зависимость от числа ключевых колонок, приведенная ниже. Число строк здесь 50000, тип данных - строка длины 10, процент добавлений, искажений и удалений по 5. Всего колонок 10.

Видно, что метод на основе соответствия, ранее показывавший неплохие результаты, теперь оказывается в аутсайдерах. Также ухудшается слияние. А вот поиск по индексу улучшается - за счет того, что сравнивать остается меньшее число колонок.

Теперь обратим внимание на несимметричность методов 1 - 4 (свертки и соединения) относительно размеров сравниваемых таблиц. Всем этим методам выгоднее, чтобы первая таблица была меньше! Это подтверждает следующая таблица, которая показывает время сравнения двух таблиц 50000 и 40000 строк в разном порядке.

На приведенной диаграмме заметен любопытный артефакт. При данном количестве строк и столбцов оказывается выгоднее добавлять в цикле 50 тысяч строк к таблице из 40 тысяч строк, чем наоборот. Возможно, это связано с особенносями выделения памяти для таблицы значений.

Ну и, наконец, исследуем зависимость времени сравнения от степени отличия таблиц. Видно, что при увеличении процента расхождений время работы свертки замедляетс. Так как начинает играть роль нелинейность сортировки.

Тесты выполнялись на платформе 8.3.5.1248 на ноутбуке VGN-Z51MRG. Полученные зависимости в целом подтверждаются на другом оборудовании, но есть и некоторые особенности, обобщить которые пока не удалось.

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

5.2 При малом размере (до 50000 строк) можно получить дополнительное ускорение свертки, применив копирование столбцов при объединении таблиц (метод 3.2).

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

5.4 В специальном случае нескольких ключевых колонок при значительном количестве различий и не отсортированности сравниваемых таблиц нужно использовать метод соединения по индексу.

5.5 Для наибольшей эффективности методов 1-4 нужно выбирать правильный порядок указания таблиц при сравнении.

5.6 В специальном случае отсортированности сравниваемых таблиц при значительном количестве различий следует использовать слияние.

5.7. В специальном случае больших (зависит от оборудования) и примерно равных по размеру таблиц, которые, к тому же, имеют значительные отличия и состоят из коротких строк и предельно малого числа колонок, возможно использовать запросы.

5.8 Если в таблицах преобладают числовые данные, даты, средние и длинные строки, то в запросах сравнения таблиц следует использовать группировку, и только для очень коротких строк - полное соединение.

6.1 В любом случае перед решающим выбором лучше по-возможности сравнивать несколько методов в реальных условиях их применения. Например, при помощи приложенной к статье обработки.

6.2 Учет особенностей данных в таблицах позволяет произвести целенаправленную дополнительную оптимизацию большинства приведенных методов. Для этого остается немало возможностей, оставшихся за пределами рассмотренного круга вопросов.

6.3 Ввод таблиц значений в запросы может занимать значительное время, что в большинстве случаев сводит на нет эффективность их применения в задачах, когда данные берутся из памяти, а не из базы. Бездумное использование запросов в этой задаче - вредное заблуждение.

6.4 Время работы метода НайтиСтроки при наличии индекса по колонкам, входящим в отбор, не зависит от размера таблицы значений. Таким образом правильной оценкой быстродействия метода сравнения таблиц с использованием соединения по индексу является O(N).

6.5 Время работы методов часто зависит от малозначительных (на первый взгляд) деталей записи программных конструкций. Поэтому для достижения предельной производительности этим деталям микрооптимизации следует уделять максимум внимания.