SQL: внутреннее соединение двух массивных таблиц

Ответ 1

Для огромных объединений, иногда явно выбирая loop join, скорость ускоряется:

SELECT EGM.Name, BioEntity.BioEntityId INTO AUX
FROM EGM 
INNER LOOP JOIN BioEntity 
    ON EGM.name LIKE BioEntity.Name AND EGM.TypeId = BioEntity.TypeId

Как всегда, публикация вашего оценочного плана выполнения может помочь нам предоставить лучшие ответы.

EDIT: если оба входа отсортированы (они должны быть с индексом покрытия), вы можете попробовать MERGE JOIN:

SELECT EGM.Name, BioEntity.BioEntityId INTO AUX
FROM EGM 
INNER JOIN BioEntity 
    ON EGM.name LIKE BioEntity.Name AND EGM.TypeId = BioEntity.TypeId
OPTION (MERGE JOIN)

Ответ 2

Я не эксперт по настройке SQL, но объединение сотен миллионов строк в поле VARCHAR не похоже на хорошую идею в любой системе базы данных, которую я знаю.

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

Ответ 3

Может быть, немного оффтопик, но: "Я заметил, что компьютер периодически застревает каждые 30 секунд (дайте или возьмите) на пару секунд".

Это поведение характерно для дешевого массива RAID5 (или, возможно, для одного диска) при копировании (а ваш запрос в основном копирует данные) гигабайт информации.

Подробнее о проблеме - не можете ли вы разбить свой запрос на более мелкие блоки? Как имена, начинающиеся с A, B и т.д. Или идентификаторы в определенных диапазонах? Это может существенно снизить затраты на транзакцию/блокировку.

Ответ 4

Во-первых, объединения 100M строк не являются вообще необоснованными или необычными.

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

Одна вещь, которую нужно попробовать: удалить INTO и посмотреть, как она выполняется. Если производительность является разумной, то для обращения к медленной записи вы должны убедиться, что ваш файл журнала БД находится на отдельном физическом томе из данных. Если это не так, голова диска будет трэш (много поисков), когда они будут читать данные и записывать журнал, а ваш перст рухнет (возможно, всего лишь от 1/40 до 1/60 того, что могло бы быть иначе).

Ответ 5

Я бы попробовал, возможно, удалить оператор "LIKE"; поскольку вы, похоже, не выполняете сопоставление подстановочных знаков.

Ответ 6

Как и было рекомендовано, я бы присвоил имя, чтобы сделать соединение более разумным. Я бы решительно рассмотрел вопрос о назначении идентификатора при импорте партий через поиск, если это возможно, поскольку это устранит необходимость сделать соединение позже (и, возможно, многократно придется выполнять такое неэффективное соединение).

Я вижу, что у вас есть этот индекс в TypeID - это очень помогло бы, если бы это было вообще выборочно. Кроме того, добавьте столбец с хэшем имени в тот же индекс:

SELECT EGM.Name
       ,BioEntity.BioEntityId
INTO AUX 
FROM EGM 
INNER JOIN BioEntity  
    ON EGM.TypeId = BioEntity.TypeId -- Hopefully a good index
    AND EGM.NameHash = BioEntity.NameHash -- Should be a very selective index now
    AND EGM.name LIKE BioEntity.Name

Ответ 7

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

Ответ 8

100 миллионов записей ОГРОМНЫ. Я бы сказал, чтобы работать с базой данных, для которой вам понадобится выделенный тестовый сервер. Использование одного и того же компьютера для выполнения других работ при выполнении таких запросов нецелесообразно.

Ваше оборудование достаточно способно, но для объединений, которые могут выполнять прилично, вам понадобится еще больше энергии. Хорошим началом станет четырехъядерная система с 8 ГБ. Кроме того, вы должны убедиться, что ваши индексы настроены правильно.

Ответ 9

У вас есть первичные ключи или индексы? можете ли вы выбрать его поэтапно? то есть, где имя типа "A%", где имя типа "B%" и т.д.

Ответ 10

Я вручную настроил некоторые индексы; как EGM, так и BioEntity имели некластеризованный индекс покрытия, содержащий TypeId и Name. Тем не менее, запрос выполнялся в течение пяти дней, и это тоже не закончилось, поэтому я попробовал запустить Database Tuning Advisor, чтобы заставить эту работу работать. Он предложил удалить старые индексы и создать статистику и два кластеризованных индекса (по одной на каждую таблицу, просто содержащую TypeId, которую я нахожу довольно нечетным - или просто тупой), но я все равно дал ему).

Вы сказали, что вы создали кластерный индекс для TypeId в обеих таблицах, хотя, похоже, у вас есть первичный ключ в каждой таблице (BioEntityId и EGMId, соответственно). Вы не хотите, чтобы ваш TypeId был кластеризованным индексом для этих таблиц. Вы хотите сгруппировать BioEntityId и EGMId (физически сортировать свои данные в порядке кластеризованного индекса на диске. Вы хотите, чтобы некластеризованные индексы на внешних ключах вы использовать для поиска. Ie TypeId. Попробуйте сгруппировать первичные ключи и добавьте некластеризованный индекс для обеих таблиц, ТОЛЬКО СОДЕРЖАЩИЙ TypeId.

В нашей среде мы имеем таблицы, которые составляют примерно 10-20 миллионов записей за штуку. Мы выполняем множество запросов, похожих на ваши, где мы объединяем два набора данных в одном или двух столбцах. Добавление индекса для каждого внешнего ключа должно значительно помочь с вашей производительностью.

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

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

Ответ 11

Повторяя несколько предыдущих сообщений (которые я проголосую)...

Насколько избирательным является TypeId? Если у вас есть только 5, 10 или даже 100 различных значений в ваших 100M + строках, индекс ничего не делает для вас - особенно, поскольку вы все равно выбираете все строки.

Я бы предложил создать столбец в CHECKSUM (Name) в обеих таблицах. Возможно, сделайте это постоянным вычисленным столбцом:

CREATE TABLE BioEntity
 (
   BioEntityId  int
  ,Name         nvarchar(4000)
  ,TypeId       int
  ,NameLookup  AS checksum(Name) persisted
 )

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

CREATE clustered INDEX IX_BioEntity__Lookup on BioEntity (NameLookup, TypeId)

(Проверьте BOL, существуют правила и ограничения для построения индексов на вычисленных столбцах, которые могут применяться к вашей среде.)

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

SELECT EGM.Name, BioEntity.BioEntityId INTO AUX
 FROM EGM INNER JOIN BioEntity 
 ON EGM.NameLookup = BioEntity.NameLookup
  and EGM.name = BioEntity.Name
  and EGM.TypeId = BioEntity.TypeId

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

Ответ 12

Почему nvarchar? Лучшей практикой является то, что если вам не нужна (или не требуется) поддержка unicode, просто используйте varchar. Если вы считаете, что самое длинное имя меньше 200 символов, я бы сделал этот столбец varchar (255). Я вижу сценарии, в которых хеширование, которое было рекомендовано вам, было бы дорогостоящим (кажется, что эта база данных интенсивно вставляется). При таком большом размере и частотном и случайном характере имен ваши индексы быстро фрагментируются в большинстве сценариев, где вы индексируете хэш (в зависимости от хэша) или имени.

Я бы изменил столбец имен, как описано выше, и сделайте кластеризованный индекс TypeId, EGMId/BioentityId (суррогатный ключ для любой таблицы). Затем вы можете хорошо присоединиться к TypeId, и "грубое" соединение на Name будет иметь меньшее количество циклов. Чтобы узнать, как долго этот запрос может выполняться, попробуйте его для очень небольшого подмножества ваших TypeIds, и это должно дать вам оценку времени выполнения (хотя оно может игнорировать такие факторы, как размер кеша, размер памяти, скорость передачи жесткого диска).

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

Ответ 13

Я попытался бы решить проблему за пределами коробки, может быть, есть и другой алгоритм, который мог бы сделать работу намного лучше и быстрее, чем база данных. Конечно, все зависит от характера данных, но есть довольно строгий алгоритм поиска строк (Boyer-Moore, ZBox и т.д.) Или другой алгоритм сбора данных (MapReduce?). Тщательно обрабатывая экспорт данных, можно было бы сгибать проблему, чтобы соответствовать более элегантному и более быстрому решению. Кроме того, можно было бы лучше распараллелить проблему, и с простым клиентом использовать простоя циклов систем вокруг вас, есть рамки, которые могут помочь с этим.

вывод этого может быть списком кортежей refid, которые вы могли бы использовать для быстрого сбора данных из базы данных.

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

мой 2 цента

Ответ 14

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

Как только вы экспортируете таблицы, напишите script, чтобы выполнить это простое соединение для вас. Для выполнения потребуется примерно столько же времени, но не будет убивать БД.

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

Для script вам нужно будет индексировать больший набор данных, затем выполнить итерацию по меньшему набору данных и выполнить поиск в большом индексе набора данных. Это будет O (n * m) для запуска.

Ответ 15

Интересно, выполняется ли время выполнения соединением или передачей данных.

EDIT: Хорошо, я вижу, что вы вставляете в таблицу Aux. Какова настройка модели восстановления базы данных?

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

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

Ответ 16

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

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

Если вам действительно нужно присоединиться к Name, вы можете рассмотреть некоторые вспомогательные таблицы, которые конвертируют имена в идентификаторы, в основном, восстанавливая денормализованный дизайн временно (если вы не можете его постоянно ремонтировать).

Идея о контрольной сумме тоже может быть хорошей, но я не играл с ней очень сильно.

В любом случае такое огромное хэш-совпадение не будет выполняться так же, как объединенные петлевые объединения. Если бы вы могли получить объединение слияния, это было бы потрясающе...