dinesh asanka (оригинал: temp tables in sql server.)
Перевод Лысенко О.В.
Введение
Временные таблицы всегда прекрасно помогали разработчикам. Раньше, когда я использовал access, я обычно создавал временные таблицы, которые удалял после решения задачи. При использовании sql server решить задачу можно гораздо проще. Не так ли?
Типы временных таблиц
Существует два типа временных таблиц. Это Локальные и Глобальные временные таблицы. Приведу пример для облегчения понимания временных таблиц. Следующий пример взят из books on line microsoft® sql server™ 2000.
"Два типа временных таблиц, локальные и глобальные, отличаются друг от друга своими именами, своей видимостью и своей доступностью. Локальные временные таблицы имеют префикс в имени в виде символа номера (#); они видны только в текущем соединении пользователя и удаляются после того, как пользователь отсоединится от экземпляра (instance) microsoft® sql server™ 2000. Глобальные временные таблицы имеют префикс имени в виде двойного символа #; они видны для любого пользователя после их создания и удаляются тогда, когда все пользователи, ссылающиеся на таблицу, отсоединятся от sql server." 1
"Например, если вы создаете таблицу с названием employees, то эта таблица может быть использована любым пользователем, который имеет разрешения в базе данных на доступ к ней до тех пор, пока она не будет удалена. Если вы создаете локальную временную таблицу с именем #employees, то только вы являетесь субъектом, который может работать с этой таблицей, которая будет удалена, как только вы отключитесь. Если вы создаете глобальную временную таблицу ##employees, любой пользователь в базе данных может работать с этой таблицей. Если никто другой не работает с этой таблицей после того, как вы ее создали, то таблица удаляется после того, как вы отсоединитесь. Если другой пользователь работает с этой таблицей после того, как вы ее создали, то таблица удаляется после того, как вы оба отсоединитесь."2
Примечание: 1,2 выше взяты из books on line microsoft® sql server™ 2000 в разделе "creating and modifying"
В отличие от использования access, вам нет необходимости удалять временные таблицы вручную, вместо этого вы можете положиться на sql server, который сделает это автоматически.
Использование временных таблиц
Временные таблицы используются для разных целей. Наиболее общее применение - это сохранение результатов вызова хранимой процедуры, уменьшение числа строк при соединениях, агрегирование данных из различных источников или замена курсоров и параметризированных представлений. Использование курсоров sql server влечет за собой большие накладные расходы. Обслуживание кода становится более простым при использовании временных таблиц в t-sql. Значительно проще отлаживать хранимую процедуру, когда вы используете временные таблицы для хранения временных данных.
Альтернативы временным таблицам
Существует несколько альтернатив временным таблицам. Одна из них - использование производных таблиц. В sql server 2000 появился новый тип данных - "table", обладающий свойствами, сходными с временными таблицами. Его основное назначение - временное хранилище набора строк. "table" играет роль локальной переменной. "table" создается в памяти в отличие от временной таблицы, которая создается в базе данных tempdb, что, очевидно, значительно быстрее. Другой факт заключается в том, что "table" использует ограниченные ресурсы по сравнению с временными таблицами.
Ограничения временных таблиц
Временные таблицы создаются в базе данных tempdb и создают дополнительную нагрузку на sql server, снижая общую производительность. sql server испытывает большие проблемы при работе с временными таблицами.
Эффективное использование временных таблиц
Если у вас нет других вариантов, кроме как использовать временные таблицы, то делайте это эффективно. Вот несколько приемов для этого:
- Включайте только необходимые столбцы и строки вместо использования всех тех столбцов и данных, которые не имеют смысла для данной временной таблицы. Всегда фильтруйте ваши данные, помещая их во временные таблицы.
- При создании временных таблиц, не используйте операторы select into. Вместо этого создавайте таблицу с помощью оператора ddl, а затем наполняйте ее данными, используя insert into.
- Используйте индексы на временных таблицах. Раньше я всегда забывал использовать индекс в тагоко рода таблицах. В особенности для больших таблиц продумывайте кластерные и некластерные индексы.
- После использования временной таблицы удаляйте ее. Это освободит ресурсы tempdb. Да, я согласен, что временные таблицы удаляются при закрытии соединения, но не дожидайтесь этого.
- Когда вы создаете временную таблицу, не делайте это в транзакции. Если вы будете создавать их внутри транзакции, это приведет к блокированию некоторых системных таблиц (syscolumns, sysindexes, syscomments). Это будет мешать другим выполнять подобные запросы.
Вывод
Вообще говоря, использования временных таблиц следует по возможности избегать. Если вам все же необходимо создать временную таблицу, то следуйте вышеперечисленным правилам, чтобы оказать мимнимальное влияние на производительность сервера.
Так ли необходимы временные таблицы?
randy dyess (оригинал: is a temporary table really necessary?)Перевод Моисеенко С.И.
На этом сайте () и не только Вы найдете статьи, рекомендующие избегать использования временных таблиц, чтобы повысить эффективность запросов. Я согласен с этим, но хотел бы добавить, что не всегда это удается сделать. Я работаю с несколькими очень большими базами данных на 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.strphonenumbercalledfrom 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 ondeclare @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 будет постоянно расти до уровня, когда вы всегда будете иметь несколько различных способов создания запроса.