Books and articles about SQL Rambler's Top100 Сменить язык на: Русский 22 September 2019 21:34:41


www.sql-ex.ru
Skip Navigation Links  

 

Print  Версия для печати

На главную страницу

Так ли необходимы временные таблицы?

Randy Dyess (оригинал: Is a Temporary Table Really Necessary?)
Перевод Моисеенко С.И.

На этом сайте (www.sqlservercentral.com) и не только Вы найдете статьи, рекомендующие избегать использования временных таблиц, чтобы повысить эффективность запросов. Я согласен с этим, но хотел бы добавить, что не всегда это удается сделать. Я работаю с несколькими очень большими базами данных на SQL Server (размер самой большой из них превышает 2.2 терабайта) и пришел к выводу, что в большинстве случаев могу обойтись без временных таблиц, хотя иногда они бывают полезны. В данной статье обсуждаются альтернативы временным таблицам с точки зрения производительности и обслуживания запросов.

Большинство литературных источников, которые рекомендуют воздерживаться от использования временных таблиц, справедливо утверждают, что это может вызывать проблемы производительности из-за блокировки tempdb во время создания временной таблицы, увеличения числа операций ввода/вывода при использовании временной таблицы и потенциальной возможности блокировки tempdb, если создание и последующие операции с временной таблицей происходят в рамках одной транзакции. И это не говоря уже о многочисленных проблемах, которые SQL Server испытывает при операциях с временными таблицами - см. список статей Knowledge Base ниже. Не подвергая вышесказанное сомнениям, я собираюсь привести некоторые доводы в пользу временных таблиц. Я признаю, что не использую или нашел причин для использования глобальной временной таблицы, поэтому Вы не найдете здесь обсуждения этих таблиц.

Для чего мы могли бы использовать временную таблицу?

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

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

Мне весьма часто приходится соединять в запросе таблицы, содержащие порядка 10, 100 и 20 миллионов строк с последующей сортировкой для вывода последних изменений в первых строках результата. Даже с надлежащими индексами и использованием предложения WHERE для фильтрации и принуждению к использованию индекса производительность запроса оказывается недопустимой (поскольку приложение, с которым я работаю, используется таким образом, что приемлемым временем отклика на запрос считаются секунды), и часто сортировка вызывает огромные потери производительности, так же как и огромную загрузку tempdb. Весьма часто оказывалось, что использование соответствующих временных таблиц для каждой из постоянных таблиц для размещения в них отфильтрованных с помощью предложения WHERE данных, прежде чем выполнять соединение и сортировку, настолько увеличивало производительность запроса, что я могу фактически внедрять это, не беспокоясь о производительности или воздействии на базу данных tempdb. Ниже приведен очень простой запрос, демонстрирующий сказанное.

Оригинальный запрос нахождения подробной информации по звонку с телефона некоего клиента:

SELECT table1.numCustID, table2.strPhoneNumber, table3.strPhoneNumberCalled
FROM dbo.table1 table1
INNER JOIN dbo.table2 table2
ON table1.numBillID = table2.numBillID
INNER JOIN dbo.table3 table3
ON table2.numBillDtlID = table3.numBillDtlID
WHERE table1.numCustID = '5555'
AND table2.strPhoneNumber = '5555555555'
AND table3.strPhoneNumberCalled = '1234561234'
ORDER BY table3.dtmCalled DESC

(Этот запрос не соответствует ни схеме, ни существующему запросу в компании Verizon. Он был создан для демонстрации конкретной проблемы на гипотетической базе данных, связанной с телекоммуникациями.)

Новый Запрос:

(Я обычно называю временную таблицу по имени хранимой процедуры, которая ее создает; поэтому я могу быстрее разобраться в любых проблемах в tempdb, связанных с использованием временных таблиц.)

CREATE TABLE #tquery2a
(multiplecolumns DATATYPES)

CREATE TABLE #tquery2b
(mulitplecolumns DATATYPES)

INSERT INTO #tquery2a
SELECT columns FROM dbo.table2 WHERE table2.strPhoneNumber = '5555555555'

INSERT INTO #tquery2b
SELECT columns FROM dbo.table3 WHERE table3.strPhoneNumberCalled = '1234561234'

SELECT table1.numCustID, #tquery2a.strPhoneNumber, #tquery2b.strPhoneNumberCalled
FROM dbo.table1 table1
INNER JOIN #tquery2a #tquery2a
ON table1.numBillID = #tquery2a.numBillID
INNER JOIN #tquery2b #tquery2b
ON #tquery2a.numBillDtlID = #tquery2b.numBillDtlID
WHERE table1.numCustID = '5555'
ORDER BY #tquery2b.dtmCalled DESC

Хотите - верьте, хотите - нет, но этот метод работает значительно быстрее, чем оригинальный запрос, особенно при наличии предложения ORDER BY.

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

Последний аргумент в пользу временной таблицы - это замена курсора. Я не люблю курсоры и призываю делать все возможное, чтобы заменить курсор (хотя и требуется оценить производительность вашего решения относительно производительности курсора). Один из трюков, который я использую заключается в подражании главной причине, по которой обычно и строится курсор, - построчный обход в цикле результирующего набора и выполнение действия, основанного на данных в той строке. Ниже - короткий запрос, который демонстрирует эту логику путем получения всех имен пользовательских таблиц и выполнения sp_spaceused на каждой таблице.

SET NOCOUNT ON
DECLARE @lngTabCount INTEGER
DECLARE @lngLoopCount INTEGER
DECLARE @strTabName SYSNAME

CREATE TABLE #tTables
(
numID INTEGER IDENTITY(1,1)
,strTableName SYSNAME
)

INSERT INTO #tTables (strTableName)
SELECT name FROM dbo.sysobjects WHERE xtype = 'u'

SET @lngTabCount = @@ROWCOUNT
SET @lngLoopCount = @lngTabCount

WHILE @lngLoopCount <> 0
BEGIN
SET @strTabName = (SELECT strTableName FROM #tTables WHERE numID = @lngLoopCount)
EXEC sp_spaceused @strTabName
SET @lngLoopCount = @lngLoopCount - 1
END

DROP TABLE #tTables
GO

Подобные курсору действия без курсора и связанные с производительностью проблемы.

Как можно работать без использования временной таблицы?

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

В SQL есть замечательная вещь, которая называется производной таблицей и которая в большинстве случаев может заменить временные таблицы. Еще раз взойду на трибуну производительности и скажу, что иногда при работе с очень большими наборами данных, производительность производных таблиц значительно ниже, чем при использовании временной таблицы с индексом. Но для большинства случаев простое использование производной таблицы в соединении устранит потребность во временной таблице. Вы можете найти несколько статей относительно использования производной таблицы на SQLServerCentral.Com, поэтому я не буду входить в детали относительно их использования в этой статье. Если Вы собираетесь заменить использование временной таблицы для организации данных из нескольких различных источников объединением (UNION) или же созданием постоянной таблицы на манер временной, оба этих подхода будут обычно удовлетворять вашим потребностям по снижению стоимости выполнения запроса. Если Вы используете SQL Server 2000 и оперируете небольшими наборами данных, пробуйте использовать новый тип данных table. Это создаст временную таблицу как объект в памяти, а не в tempdb и улучшит производительность вашего запроса. Проанализируйте использование коррелированого подзапроса, не сможет ли он заменить вашу временную таблицу. Иногда лишь повторное обращение к местонахождению ваших данных заменит потребность во временных таблицах.

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

Если Вы используете временные таблицы, оптимизируйте их использование.

Если ситуация диктует использование временной таблицы, тогда следует сделать несколько вещей, чтобы улучшить ее производительность. Сначала, именно потому, что это временная таблица, не пытайтесь поместить в нее все столбцы и все строки из вашей постоянной таблицы, если Вы не нуждаетесь в них. Фильтруйте данные, поступающие в вашу временную таблицу, чтобы ограничиться минимальным числом необходимых столбцов и строк. Во вторых, не используйте оператор SELECT INTO для создания временных таблиц. Оператора SELECT INTO в коде следует избегать любой ценой из-за блокировки, которую он накладывает на системные объекты в течение времени пока решается, как строить таблицу. Найдите время, чтобы написать скрипт создания временной таблицы и отдельных операторов INSERT INTO для ее заполнения. Я считаю, что можно воспользоваться SELECT INTO, если он включает WHERE 1=0, для создания таблицы наиболее быстрым способом, однако не стоит этого делать только для того, чтобы сэкономить на нескольких нажатиях клавиш. В-третьих, посмотрите, как Вы используете временные таблицы, чтобы избежать повторной компиляции хранимой процедуры. Я объясняю это подробно в статье Optimizing Stored Procedure Recompiles, доступной на моем вебсайте. В-четвертых, проверьте необходимость в кластеризованном индексе на вашей временной таблице. Если набор данных будет большим, то кластеризованный индекс ускорит операции выборки из временной таблицы, однако Вы должны взвесить расходы производительности на создание этого индекса и вставку данных в таблицу с кластеризованным индексом. Это один из тех методов, который необходимо должен проверяться перед решением о выборе индекса на обеих вариантах с максимально возможным набором данных, который, на ваш взгляд, будет помещаться во временную таблицу. Наконец, известно, что когда заканчивается выполнение хранимой процедуры и завершается подключение, временная таблица удаляется, но зачем сохранять ее, если она уже не нужна. Если Ваш код создает и использует временную таблицу, а затем переключается на другие вещи, в которых не используется данная таблица, удалите ее в коде. Это освободит ресурсы tempdb для других объектов. Я удаляю таблицу в конце хранимой процедуры даже тогда, когда подключение заканчивается, только для того, чтобы избежать любых проблем, которые могут возникнуть из-за неизвестных ошибок.

Резюме

Хотя временные таблицы (по моему мнению) намного лучше курсоров, их использование действительно вызывает падение производительности. В этой статье кратко обсуждается ряд причин для использования временных таблиц и несколько альтернативных приемов. Конкретное решение следует принимать в зависимости от ситуации. Проверьте ваш запрос при использовании альтернативных подходов прежде, чем Вы создадите временную таблицу, и оцените производительность на временных таблицах; после чего вы сможете сделать обоснованный выбор. Я твердо придерживаюсь той точки зрения, основанной на накопленном опыте (и это даже при том, что я пишу эту статью), что прежде, чем применять что-либо вычитанное в книге или на вебсайте, необходимо проверить это несколькими различными способами. Делайте это и ваше мастерство в использовании Transact-SQL будет постоянно расти до уровня, когда вы всегда будете иметь несколько различных способов создания запроса.

2002

См. также:

Dinesh Asanka "Временные таблицы в SQL Server."

На главную страницу

Print  Версия для печати


Usage of any materials of this site is possible
only under condition of mandatory allocation of the direct link to a site
http://www.sqlbooks.ru
on each page where used materials are placed.

 Main   Articles    Books 
Рейтинг@Mail.ru Rambler's Top100 Alt Упражнения по SQL: обучение, тестирование, сертификация по языку SQL Copyright c 2002-2006. All rights reserved.