Есть ли смысл использовать checksum или hashbyte в JOIN'ах?

Question

[zhaar]

Собственно "простой" вопрос - рассмотрим 3 ситуации работы Left Join'ов. В качестве исходных данных есть простая табличка из 3-х колонок на 10к записей, состоящая из даты (date), текста (varchar(1000) и чисел (int) (tbl1), а так же вторая табличка на 100к записей с кучей столбцов в т.ч. и тех, которые нужны для джойна.
Задача - найти различия в данных и вывести их пользователю.

Что будет быстрее работать? Вопрос чисто теоретический.

1) Left join, в котором явно указываются связи по каждому полю, т.е.

left join tbl2 on tbl1.date=tbl2.date and tbl1.text=tbl2.text and tbl1.size=tbl2.size

2) Left join, где используются concat из всех полей, чтобы собрать одну длинную строчку для джойна по ней, т.е.:

left join tbl2 on concat(tbl1.date,tbl1.text,tbl1.size) = concat(tbl2.date,tbl2.text,tbl2.size)

3) Left join, где используются хэш-функция или проверочная сумма, т.е.

left join tbl2 on checksum(tbl1.date,tbl1.text,tbl1.size) = checksum(tbl2.date,tbl2.text,tbl2.size)

Answer 1

[Valentin Avramko]

Однозначно первый вариант. А совсем правильно - это по этим полям еще и индексы построить.

Answer 2

[Sumor]

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

PS. Для академического интереса можно построить БД и данные так, что каждый из трёх вариантов будет выигрывать.

Answer 3

[zhaar]

Из того, что проверил сам результаты почти не отличаются. Единственное что выяснил, что checksum для джойнов лучше не использовать, т.к. часто дает "коллизии" из-за которых дублируются связи.
Добавление индексов хорошо помогает на совсем больших таблицах, где куча данных (например, за бОльшее число периодов, чем указано в условии where)

Answer 4

[Руслан .]

Можно в таблице tbl создать поле в котором хранить checksum(tbl.date,tbl.text,tbl.size) и сделать по этому полю индексацию.

Если checksum дает много коллизий можно попробовать другой хэш, в MSSQL например их вот сколько вариантов:
MD2 | MD4 | MD5 | SHA | SHA1 | SHA2_256 | SHA2_512

https://docs.microsoft.com/ru-ru/sql/t-sql/functio...

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

У меня есть кейс, где нужно искать совпадения по 17 аналитикам и там такой способ ускорил поиск в 10 - ки раз по сравнению с обычным поиском по куче полей, в вашем случае всего 3 поля и наверное join по полям и наличие селективного индекса хотя бы по одному из них уже достаточно.
По полю с varchar(1000) лучше не делать индекса, а по двум другим вполне можно.