BULK INSERT для нескольких связанных таблиц?
Мне нужно сделать BULK INSERT из нескольких сотен тысяч записей в 3 таблицы. Простая разбивка таблиц будет:
TableA
--------
TableAID (PK)
TableBID (FK)
TableCID (FK)
Other Columns
TableB
--------
TableBID (PK)
Other Columns
TableC
--------
TableCID (PK)
Other Columns
Проблема с массовой вставкой, конечно, заключается в том, что она работает только с одной таблицей, поэтому FK становятся проблемой.
Я искал способы обойти это, и из того, что я почерпнул из разных источников, использование столбец ПОСЛЕДОВАТЕЛЬНОСТЬ может быть лучшим выбором. Я просто хочу убедиться, что я правильно собрал логику из различных тем и сообщений, которые я читал по этому вопросу. Дайте мне знать, если у меня есть правильная идея.
Во-первых, изменить таблицы, чтобы они выглядели так:
TableA
--------
TableAID (PK)
TableBSequence
TableCSequence
Other Columns
TableB
--------
TableBID (PK)
TableBSequence
Other Columns
TableC
--------
TableCID (PK)
TableCSequence
Other Columns
Затем из кода приложения я сделал бы пять обращений к базе данных со следующей логикой:
Запросить X порядковых номеров из TableC, где X — известное количество записей, которые нужно вставить в TableC. (1-й вызов БД.)
Запросить Y порядковых номеров из таблицы B, где Y — известное количество записей, которые должны быть вставлены в таблицу B (2-й вызов БД).
Измените существующие объекты для A, B и C (которые являются моделями, сгенерированными для зеркалирования таблиц) с известными порядковыми номерами.
Массовая вставка в TableA. (3-й вызов БД)
- Массовая вставка в TableB. (4-й вызов БД)
- Массовая вставка в TableC. (5-й вызов БД)
И затем, конечно, мы всегда присоединялись к Последовательности.
У меня три вопроса:
Верна ли у меня основная логика?
В таблицах B и C следует ли удалить кластеризованный индекс из PK и вместо этого добавить последовательность?
Когда порядковые номера запрашиваются из таблиц B и C, блокируются ли они каким-либо образом между запросом и массовой вставкой? Мне просто нужно убедиться, что между запросом и вставкой какой-то другой процесс не запрашивает и не использует те же числа.
Спасибо!
Обновлено:
Напечатав это и опубликовав, я углубился в документ ПОСЛЕДОВАТЕЛЬНОСТЬ. Я думаю, что я неправильно понял это сначала. SEQUENCE не является типом столбца. Для фактического столбца в таблице я бы просто использовал INT (или, может быть, BIGINT) в зависимости от количества записей, которые я ожидаю иметь). Фактический объект SEQUENCE — это совершенно отдельная сущность, чья работа состоит в том, чтобы генерировать числовые значения по запросу и отслеживать, какие из них уже были сгенерированы. Итак, если я правильно понимаю, я бы сгенерировал два объекта SEQUENCE, один для использования в сочетании с таблицей B, а другой — с таблицей C.
Так что это ответ на мой третий вопрос.
Ответы 2
Do I have the basic logic correct?
да. Другим распространенным подходом здесь является массовая загрузка данных в промежуточную таблицу и выполнение чего-то подобного на стороне сервера.
У клиента вы можете запросить диапазоны значений последовательности, используя хранимую процедуру sp_sequence_get_range.
In Tables B and C, would I remove the clustered index from the PK
Нет, как вы позже заметили, последовательность просто предоставляет вам значения PK.
Хорошо, хорошо... спасибо. Да, я только что нашел документы для sp_sequence_get_range прямо перед тем, как вы прокомментировали. Не могли бы вы сделать мне одолжение и рассказать, как будет работать процесс использования промежуточных таблиц? Я в основном озабочен тем, чтобы PK и FK совпадали в трех таблицах.
Это зависит от того, как выглядят исходные данные и есть ли в таблицах TableB и TableC какие-либо уникальные столбцы. Если это так, вы можете просто загрузить их, а затем, когда вы загружаете TableA, найдите PK на основе соответствия уникальному столбцу.
Данные в B и C просты. Нет уникальных столбцов. Но я все еще не получаю большую картину здесь. Скажите, имею ли я на это право... У меня есть 3 таблицы в БД, которые отражают A, B и C. Назовем их TempA, TempB и TempC. Из кода я делаю необработанный дамп трех моделей в таблицы Temp. Итак, теперь TempA, TempB и TempC полны данных, но они никак не связаны. Имею ли я это право до сих пор? Тогда... куда мне идти дальше?
(это было бы намного проще в таблице NoSQL, где я мог бы просто хранить большие двоичные объекты JSON!)
При желании вы можете хранить большие двоичные объекты JSON в SQL Server. Действительно больше решения моделирования данных. docs.microsoft.com/en-us/sql/relational-databases/json/… Разделение таблиц обеспечивает нормализацию (каждый фрагмент данных сохраняется только один раз) и обеспечивает ссылочную целостность.
Извините, сначала неправильно прочитал ваш вопрос. Теперь я вижу, что вы пытаетесь сгенерировать свои собственные PK, а не позволить MS SQL сгенерировать их для вас. Сотрите мой комментарий выше.
Как упомянул Дэвид Браун, вы можете использовать промежуточную таблицу, чтобы избежать нагрузки на кучу вашего приложения. Используйте базу данных tempdb и вносите изменения непосредственно в таблицу, используя одну транзакцию для каждой таблицы. Затем скопируйте промежуточные таблицы в их цель или используйте MERGE при добавлении. Если вы применяете FK, вы можете временно удалить эти ограничения, если решите вставлять в обратном порядке (C=>B=>A). Вы также можете рассмотреть возможность временного удаления индексов, если во время вставки возникают проблемы с производительностью. Наконец, рассмотрите возможность использования SSIS вместо пользовательского приложения.
Спасибо. Я почти уверен, что следил за всем этим. Но я также должен сказать, что это звучит намного сложнее (и, возможно, дороже), чем процесс, который я описал в своем ОП. Нет? Помогите мне понять, чего я не вижу.
Похоже, Дэвид проводит вас через временную таблицу. Основные моменты: используйте tempdb для создания 3 промежуточных таблиц (это очень похоже на создание обычной таблицы), массовая вставка в эти таблицы, затем внесите в них изменения, отправив операторы обновления в одном транше для каждой таблицы. После того, как вы закончите, скопируйте или ОБЪЕДИНИТЕ их с фактическими таблицами, в которые вы хотите получить данные, а затем удалите таблицы tempdb. Два других пункта — временное снятие ограничений и отключение индексов — относятся к целевым таблицам. Временные таблицы не должны создаваться ни с одной из тех, с которых нужно начинать.
Хорошо, я могу ясно представить себе весь этот процесс в своей голове, но для одной (очень важной) части ... «затем внесите в них изменения». Что именно это значит? Я до сих пор не понимаю, как заставить PK и FK совпадать.
Кстати, я ОЧЕНЬ ценю вашу помощь!
Есть разные способы сделать это, и это действительно зависит от ваших данных. Но суть в том, что для каждой таблицы вы создаете оператор обновления в коде для каждой строки. Затем вы отправляете все эти операторы обновления сразу на сервер в одной транзакции.
Нп. Посмотрите на это, если вы не знакомы с транзакциями: docs.microsoft.com/en-us/sql/t-sql/language-elements/…
Извините за комментарии строки .. только что увидел ваши другие вопросы относительно сложности и стоимости. Вы правы, что этот подход сложнее, чем ваш ОП. Но, это намного дешевле. Люди используют этот подход, потому что он значительно снижает стоимость, которая становится существенной, когда вы пытаетесь извлечь и обработать 100 000 строк в памяти.
Хорошо, хорошо знать! Я думаю, что закодирую оба метода, а затем поставлю секундомер для проверки. Прямо сейчас я не понимаю, как метод, который вы объяснили, может быть дешевле, чем то, что составляет не более чем 3 оператора массовой вставки. Но, я собираюсь попробовать оба.
Не совсем уверен, почему вы хотите использовать последовательности. Похоже, вам нужны автоматически сгенерированные PK (AI) из таблицы C, чтобы выполнять вставки в таблицу B, а затем AI из таблицы B, чтобы выполнять вставки в таблицу A. Если вы пытаетесь предсказать какие ИИ будут для каждой таблицы с использованием последовательности, не надо. Есть лучшие способы предсказать следующий ИИ. Но даже они могут быть неправильными, если есть сбои транзакций, коллизии и т. д. Кроме того, нельзя быть на 100% уверенным, что массовая вставка верна для вашего решения. Можете ли вы рассказать больше о том, откуда берутся данные, которые вы вставляете?