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

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

Предполагается, что средний размер данных в столбце «Имя» составляет 150 символов, в действительности у вас будет 300 байт плюс другие столбцы на запись. Умножьте это на 100 миллионов записей, и вы получите около 30 ГБ данных для передачи вашему клиенту. Вы запускаете клиент удаленно или на самом сервере? Возможно, вы ждете 30 ГБ данных, передаваемых вашему клиенту ...

РЕДАКТИРОВАТЬ: Хорошо, я вижу, вы вставляете в таблицу Aux. Какова настройка модели восстановления базы данных?

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

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

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

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

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

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

К. Скотт здесь есть довольно хорошая статья , которая более подробно объясняет некоторые вопросы.

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

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

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

Я вижу, у вас есть этот индекс для 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В зависимости от многих факторов он все равно будет работать долго (не в последнюю очередь потому, что вы '

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

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