(Реальная) разница между операторами ‘SELECT… INTO’ и ‘INSERT… SELECT’
В этом сообщении блога я исследую разницу между операторами ‘SELECT… INTO’ и ‘INSERT… SELECT’. Недавно я переместил некоторые данные для клиента в совершенно новую таблицу из-за плохого дизайна исходной таблицы. Сначала я создал целевую таблицу, а затем выполнил оператор «INSERT… SELECT». Сначала я запустил это в тестовой среде, где выполнение запроса заняло больше часа, а файл журнала транзакций базы данных увеличился на 50 ГБ, хотя размер таблицы составлял всего 18 ГБ. Коллега предложил вместо этого использовать оператор «SELECT … INTO», и это заставило меня задуматься: почему это быстрее или лучше?
Наиболее очевидная мысль заключается в том, что первый работает быстрее, потому что он выигрывает от «минимального ведения журнала» и автоматически создает целевую таблицу. Конечно, «SELECT … INTO» создает целевую таблицу «на лету» на основе результата оператора SELECT, однако мне было любопытно, почему это происходит быстрее.
В целях тестирования я использовал кластеризованную таблицу FactProductInventory в базе данных AdventureWorksDW2012. В таблице 776 286 строк, общий размер которых составляет 41 МБ. База данных находилась в модели восстановления FULL, и ее файл журнала транзакций был 109.МБ с 98,3% свободного места.
Я включил «СТАТИСТИЧЕСКОЕ ВРЕМЯ» и «СТАТИСТИЧЕСКОЕ ВВОД-ВЫВОД», чтобы просмотреть статистику запроса (время выполнения и ЦП) и дискового ввода-вывода (чтение и запись). Я также отслеживал использование пространства журнала, просматривая представление «DBCC SQLPERF(LOGSPACE)». Для тех, кому интересно, функция fn_dblog() предоставляет более подробную информацию, которую я лично считаю более полезной, поскольку вы читаете журнал транзакций и видите построчно, что происходит.
SELECT … INTO Первым тестом, который я провел, был оператор «SELECT … INTO», который выбрал все строки (776 286) из «FactProductInventory» в целевую таблицу «FactProductInventoryNew».
Вот статистика:
Как видно из приведенных выше результатов, SQL Server помещает страницы исходной таблицы в буферный кеш (упреждающее чтение), а затем выполняет 5231 логическое чтение, что именно вы ожидаете увидеть. В таблице 5231 страница (1 IAM, 18 индексных страниц и 5212 страниц данных) и, учитывая, что все было выбрано, SQL-серверу пришлось просмотреть всю таблицу. Продолжительность составляла примерно 1,3 секунды при загрузке ЦП 1 секунду.
Журнал транзакций не увеличился, но на приведенном выше снимке экрана видно, что были использованы дополнительные 37,5% (40,8 МБ) его пространства, что примерно соответствует размеру таблицы. Таким образом, все выбранные строки были полностью зарегистрированы в файле журнала транзакций. Имейте в виду, что это не минимально регистрируемая операция, поскольку база данных находится в модели восстановления FULL.
Чтобы воспользоваться преимуществами минимального ведения журнала, база данных должна быть в моделях восстановления BULK_LOGGED или SIMPLE, после чего SQL Server отслеживает только выделение экстентов и изменения метаданных.
Перед началом следующего теста, чтобы убедиться, что у меня есть чистая отправная точка, я восстановил базу данных из резервной копии. Затем я выполнил оператор «INSERT … SELECT», перед которым мне нужно было создать новую таблицу с той же структурой, что и исходная.
Результаты: много).
Логические операции чтения исходной таблицы были такими же, однако я заметил кое-что странное. Как видно из выделенной выше статистики, SQL Server выполнил 781 495 операций чтения с целевой таблицей! Мы только вставили данные, так зачем SQL Server читать из целевой таблицы?
Поскольку целевая таблица уже существует, SQL Server должен считывать страницу целевой таблицы для каждой отдельной строки, чтобы установить, куда будет вставлена строка. Как вы можете видеть выше, дополнительные 5209чтения были выполнены по сравнению с количеством строк в таблице (776 286). Это количество раз, которое SQL Server должен прочитать страницу PFS.
PFS (свободное пространство страницы) отслеживает свободное пространство для значений больших объектов и страниц данных кучи, а 1 страница PFS существует для 8088 страниц базы данных или около 64 МБ.
Кроме того, SQL Server должен заблокировать эту страницу, чтобы предотвратить изменение или вставку строк на той же странице другими транзакциями. Обратите внимание, что эти операции (блокировки, изменения страниц данных, чтение PFS и т. д.) должны отслеживаться в файле журнала транзакций вместе со вставленными строками, поэтому он занимает больше места в файле журнала транзакций. Для тех, кто заинтересован, следующий запрос может быть выполнен для получения вышеуказанной информации (при условии, что ваш оператор вставки является единственным, работающим с вашей базой данных):
INSERT … SELECT WITH(TABLOCK)
В этот момент мне было любопытно, что произойдет, если у меня будет эксклюзивная блокировка таблицы. Итак, после восстановления базы данных я выполнил следующую инструкцию:
Вот статистика:
Поскольку SQL Server теперь имеет монопольный доступ ко всей таблице, ему больше не нужно беспокоиться о параллелизме и блокировке, и он может просто продолжить и сбросить данные в таблицу.
Глядя на статистику, мы видим, что «INSERT SELECT» работает точно так же, как оператор «SELECT INTO», включая использование пространства журнала транзакций.
Выводы
В модели ПОЛНОГО восстановления нет минимально регистрируемых операций. Поскольку «SELECT… INTO» создает целевую таблицу, он владеет исключительно этой таблицей и работает быстрее по сравнению с «INSERT… SELECT». Поскольку «INSERT … SELECT» вставляет данные в существующую таблицу, это происходит медленнее и требует больше ресурсов из-за большего количества логических операций чтения и большего использования журнала транзакций. Однако, предоставляя подсказку запроса для блокировки всей целевой таблицы, эти два оператора работают одинаково.
Подписаться на обновления по электронной почте
+44 (0)20 3051 3595 | [email protected]
Свяжитесь с нами
Нажав кнопку «Отправить» ниже, вы разрешаете Coeo хранить и обрабатывать личную информацию, предоставленную выше, для предоставления вам запрошенного контента.
Вы можете отказаться от подписки на эти сообщения в любое время. Для получения дополнительной информации о том, как отказаться от подписки, и о нашей приверженности вашей конфиденциальности, пожалуйста, ознакомьтесь с нашими Политика конфиденциальности .
Посмотреть все события
NOW Building, Thames Valley Park Drive, Reading, RG6 1RB
INSERT/SELECT в Teradata — забыть код
Авторизоваться Зарегистрироваться
- Войти
- Зарегистрироваться
- Забыть код
- Терадата
- ВСТАВИТЬ/ВЫБРАТЬ
| Эта опция говорит: Если данные уже присутствуют на вашем складе, почему вы перезагружаетесь? Почему вы вставляете значения? |
|---|
Для вставки всех записей во все столбцы.
ВСТАВИТЬ В таблицу2 ВЫБЕРИТЕ * ИЗ таблицы1Для вставки записей в меньшее количество столбцов.
ВСТАВИТЬ В таблицу2 ВЫБЕРИТЕ (столбец1, столбец2, столбец3) ИЗ таблицы1; или INSERT INTO table2 (столбец, столбец b, столбец c) ВЫБЕРИТЕ (столбец1, столбец2, столбец3) ИЗ таблицы1;
Вы можете использовать функции SUB QUERIES, AGGREGATE также в операторах SELECT, но тип данных между источником и целью должен быть одинаковым.
Несмотря на это, очень важно, чтобы список выбранных значений данных соответствовал последовательности столбцов в CREATE TABLE.