Ms sql табличная переменная: MS SQL Server и T-SQL

Содержание

Производительность табличных переменных — Статьи : Персональный сайт Михаила Флёнова

29 Aпреля 2016
Базы данных

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

Итак, вы можете написать что-то типа: 

declare @t as table (int value)

Здесь мы объявляем переменю @t, которая будет представлять собой таблицу и будет состоять из одной колонки value типа число. Можно создавать более сложные таблицы, но для нашего примера в этой статье и для разговора про оптимизацию достаточно одной колонки. 

Теперь можно использовать эту переменную в своих запросах. Можно записать в нее кучу данных, можно выполнять выборки данных из этой переменной:

insert into @t

select UserID

from User

или

select * from @t

Я заметил, что табличные переменные используют, когда нужно выбрать данные для большой выборки. Например, у вас в коде есть запрос, который возвращает пользователей сайта. Теперь вы собираете все ID пользователей, помещаете в табличную переменную и можете искать все адреса для этих пользователей. Может быть кто-то скажет — что за бред и почему не выполнить одним запросом к базе и получить сразу все? У меня ростов слишком простой пример. Ну допустим, что пользователи приходят от Web сервиса, а адреса у вас в базе данных. Опа. Тут уже выхода нет. От сервиса мы получили кучу ID пользователей и чтобы для каждого из них н выполнять запрос к базе, кто-то решает, что проще засунуть все ID в параметр запроса в виде табличной переменной и запрос будет выглядеть круто:

select *

from @t as users 

   join Address a on a.UserID = users.UserID

Все это красиво работает, потом в коде C# вы можете слить результаты обоих  массивов данных в один объект. С помощью LiINQ это пишется за пять минут (хотя я видел далеко не эффективные реализации) и вроде бы никаких проблем…. Кроме производительности самого запроса.  

Дело в том, что переменные в виде таблиц не проектировались для того, чтобы обрабатывать большие данные. Если я не ошибаюсь, но оптимизатор запросов не будет заморачиваться, а все время будет использовать метод выполнения LOOP. То есть для каждого ID из @t будет происходить поиск в таблице Address. Если в @t находиться 1000 записей, то сервер 1000 раз будет сканировать Address. Я сейчас работаю над запросом, который выглядит примерно так же, но просто больше задействовано таблиц и у меня именно так и получилось. В плане выполнения нереальное количество сканов и сервер просто умирает в попытке найти данные. 

На много эффективнее взять просканировать всю таблицу Address и найти всех пользователей сразу. Этот метод называется MERGE, но его SQL Server в основном выбирает, когда данных очень много и когда они отсортированы. В данном случае оптимизатор понятия не имеет, какие данные будут в переменной, сколько их и тем более не представляет, есть ли сортировка, ведь на такой переменной нет индексов.  

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

Даже если просто заменить табличную переменную на SQL, это значительно увеличит скорость работы запроса:

select *

from (

 Select 10377 as UserID

 Union all

 Select 73736

 Union all

 Select 7474748

 ….

  ) as users 

   join Address a on a.UserID = users.UserID

Таких SELECT может быть даже тысяча и текст запроса будет огромным, но он будет выполняться в тысячи раз быстрее на большом количестве данных, потому что SQL Server сможет выбрать эффективный план выполнения. 

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

Неужели MS не подумали о нас? Подумали. Я думаю, они не ожидали, что пользователи будут так использовать табличные переменные, но красивый обходной манёвр есть. 

Есть несколько вариантов решения проблемы, но на мой взгляд самый эффективный с точки зрения производительности (из личного опыта, возможно я не прав) — добавить в конец запроса OPTION (RECOMPILE): 

select *

from @t as users 

   join Address a on a.UserID = users.UserID

OPTION (RECOMPILE)

Эта опция ставиться один раз в самом конце запроса, даже после ORDER BY, если он есть. Смысл этой опции в том, чтобы заставить SQL Server перекомпилировать запрос при каждом выполнении. 

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

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

Плохо ли использовать переменные в виде таблиц? Нет. Используйте. Просто помните, что они не создавались для больших данных. Иногда лучше создать временную таблицу, если данных уж совсем много и поместить данные туда, может быть даже создать налету индекс. Мне приходилось делать такие вещи в случае с отчётами, правда всего один раз. Тогда я время генерации одного отчёта сократил на работе с 3 часов до 20 минут. 

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

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

Передача таблицы в хранимую процедуру

SQL: Передача таблицы в хранимую процедуру

Введение

SQL Server 2005 и более ранние версии не поддерживают передачу табличных переменных в хранимую процедуру.

Рассмотрим в этом посте функционал SQL Server 2008 и более поздних версий, позволяющий передавать таблицу в хранимую процедуру или функцию.

Код

Перед созданием функции или хранимой процедуры, в которую будем передавать табличную переменную, нужно определить пользовательский табличный тип, который был введен в SQL Server 2008. Табличный тип представляет из себя табличную структуру.

Итак, первый этап – создание пользовательского табличного типа. Ниже в коде TSQL создается пользовательский табличный тип ItemInfo:

CREATE TYPE ItemInfo AS TABLE
(
 ItemId VARCHAR(50),
 Qty INT
)

Для просмотра созданного типа можно использовать системное представление SYS. TYPE. Запрос ниже возвращает все определенные в системе типы:

SELECT * FROM SYS.TYPES

Для вывода данных только по табличным типам этот запрос используется следующим образом:

SELECT * FROM SYS.TYPES WHERE is_table_type = 1

Еще данные по табличным типам можно получить таким способом:

SELECT * FROM SYS.TABLE_TYPES

Структуру самой табличной переменной можно посмотреть следующим образом:

SELECT * 
FROM SYS.COLUMNS c
JOIN SYS.TABLE_TYPES tt ON c.[object_id] = tt.type_table_object_id 
WHERE tt.name = 'ItemInfo'

Итак, у нас есть необходимый табличный тип. Теперь создадим переменную с типом ItemInfo и попробуем вставить в нее несколько записей. Затем запросим данные из переменной для проверки корректности вставки:

DECLARE @items AS ItemInfo
INSERT INTO @items (ItemId, Qty)
	SELECT 'A0000001', 10 UNION ALL
	SELECT 'B0000001', 20 UNION ALL
	SELECT 'C0000001', 30
SELECT * FROM @items

Результат:

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

CREATE PROCEDURE sp_SelectItemInfo
(
	@Items	ItemInfo
)
AS
SELECT * FROM @Items

Упс, получили ошибку:

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

CREATE PROCEDURE sp_SelectItemInfo
(
	@Items	ItemInfo READONLY
)
AS
SELECT * FROM @Items

Теперь выполним созданную хранимую процедуру —
запустим следующий код:

DECLARE @Items AS ItemInfo
INSERT INTO @items (ItemId, Qty)
	SELECT 'A0000010', 100 UNION ALL
	SELECT 'B0000010', 200 UNION ALL
	SELECT 'C0000010', 300
EXECUTE sp_SelectItemInfo @Items

Результат:

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

CREATE PROCEDURE sp_SelectItemInfo1
(
	@Items	ItemInfo READONLY
)
AS
SELECT * FROM @Items
INSERT INTO @Items(ItemId, Qty)
	SELECT 'D0000010', 400

Ошибка:

Заключение

С табличными переменными работать довольно просто, важно только помнить, что нельзя менять/добавлять данные в получаемой переменной.

Подписаться на:
Комментарии к сообщению (Atom)

Табличная переменная в SQL Server

Введение

В предыдущей статье я описал временные таблицы в SQL. Таким образом, есть еще один альтернативный подход, который позволяет делать то же самое, что и временная таблица. Альтернативой временной таблице является «Переменная таблицы». Мы можем выполнять аналогичные операции с помощью табличных переменных, но есть разница, которую мы увидим далее в этой статье.

Табличная переменная в SQL Server

Табличная переменная — это переменная, в которой может храниться полная таблица данных. Это похоже на табличную переменную, но, как я уже сказал, табличная переменная является переменной. Так как же нам объявить переменную в SQL? Использование символа @. То же самое верно и для табличной переменной. поэтому синтаксис табличной переменной выглядит следующим образом:

 Объявить @<Имя_переменной> TABLE(
Имя_столбца [Тип_данных],
Имя_столбца [Тип_данных],
Имя_столбца [Тип_данных],
. .....
)  

Для Пример 

 Объявить @TempTable TABLE(
идентификатор целое,
Название varchar(20)
)    

Теперь вы можете выполнять вставку, обновление, удаление и выбор всех операций с ним, как показано ниже, я пишу это так:

 Declare @TempTable TABLE(
идентификатор целое,
Название varchar(20)
)

вставить в значения @TempTable(1,'Sourabh Somani')
вставить в значения @TempTable (2, 'Шайли Дашора')
вставить в значения @TempTable (3, "Дивья Шарма")
вставить в значения @TempTable (4, 'Свати Сони')

Выберите * из @TempTable  

Разница между временными таблицами и табличной переменной

Между временными таблицами и временными переменными есть разница, это:

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

    Пример

  2. Операция транзакции (фиксация и откат) невозможна в переменной таблицы, но во временной таблице мы можем выполнять транзакции (фиксация и откат).

    Пример 

     Объявить @TempTable TABLE(
идентификатор целое,
Название varchar(20)
)
начать транс T
вставить в значения @TempTable(1,'Sourabh Somani')
вставить в значения @TempTable (2, 'Шайли Дашора')
вставить в значения @TempTable (3, "Дивья Шарма")
вставить в значения @TempTable (4, 'Свати Сони')
зафиксировать транзакцию T
Выберите * из @TempTable  

    или 

     Объявить @TempTable TABLE(
идентификатор целое,
Название varchar(20)
)
начать транс T
вставить в значения @TempTable(1,'Sourabh Somani')
вставить в значения @TempTable (2, 'Шайли Дашора')
вставить в значения @TempTable (3, "Дивья Шарма")
вставить в значения @TempTable (4, 'Свати Сони')
откат транзакций T
Выберите * из @TempTable  

Важные замечания о табличных переменных

  • То же, что и временная таблица.
  • Одиночный запрос не может быть выполнен.
  • Если мы хотим выполнить несколько операций, используйте табличную переменную, в противном случае, если это операция с огромным объемом данных, используйте временную таблицу.
  • Фиксация и откат (транзакция) невозможны с табличными переменными, поэтому, если вы хотите выполнить операцию транзакции, всегда используйте временные таблицы.
  • Разница между переменной таблицы и временной таблицей
  • SQL Server
  • Переменная таблицы SQL
  • Временная таблица в SQL

Как создать табличную переменную в SQL Server

Время чтения: 4 минуты

В этом очень кратком учебник, мы обсудим, как вы можете создать табличную переменную в Microsoft SQL Server. Знание того, как создать табличную переменную SQL Server, может быть очень полезным при запросе данных в базе данных. Это очень просто, и вам нужно знать всего несколько правил.

Без лишних слов,

Как создать табличную переменную SQL Server:

Давайте начнем с примера создания обычной таблицы . Мы создадим простую таблицу Products :

 CREATE TABLE Products
(
ProdID INT INDEX idx_ProdID CLUSTERED,
ProductName VARCHAR(20) INDEX idx_ProductName NONCLUSTERED,
ProductPrice ДЕСЯТИЧНОЕ (5,2),
В НАЛИЧИИ БИТ ПО УМОЛЧАНИЮ(1)
)

Создать эту таблицу как таблицу переменная вместо этого мы просто заменяем CREATE TABLE Products на DECLARE @Products TABLE . Вот так:

 DECLARE @Products TABLE
(
ProdID INT INDEX idx_ProdID CLUSTERED,
ProductName VARCHAR(20) INDEX idx_ProductName NONCLUSTERED,
ProductPrice ДЕСЯТИЧНОЕ (5,2),
В НАЛИЧИИ БИТ ПО УМОЛЧАНИЮ(1)
)

Чтобы лучше понять это, давайте вернемся к тому, как создать обычную переменную, такую ​​как переменная типа INT:

DECLARE @someInteger INT

При создании обычной переменной мы используем ключевое слово «DECLARE», за которым следует имя переменной, которую мы хотим создать, с символом « @ » перед ним, за которым следует тип данных .

Когда дело доходит до переменной таблицы , мы по-прежнему используем ключевое слово «DECLARE», за которым следует имя переменной, которую мы хотим создать, с символом « @ » перед ним, за которым следует тип данных, который будет просто «ТАБЛИЦА».

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

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

После того, как мы создали эту табличную переменную, мы можем сделать почти все, что мы сделали бы с обычной таблицей. Мы добавим в таблицу несколько строк:

Один из распространенных способов заполнения табличной переменной — использование предложения INSERT INTO…SELECT. Например, следующий запрос вставит все содержимое моей обычной таблицы Products в нашу таблицу 9.0095 переменная @Products таблица:

Конечно, вы также можете выполнять ОБНОВЛЕНИЕ и УДАЛЕНИЕ строк в переменной таблицы.

Некоторые правила, которые необходимо знать

При использовании табличных переменных необходимо знать некоторые правила.

1) Объем табличной переменной очень мал Табличные переменные существуют только в памяти, и они выпадают из области видимости, когда пакет заканчивается.

Например, давайте вернемся к последнему оператору INSERT INTO…SELECT:

Обратите внимание, что у меня есть все выделенный код, что означает, что все они выполняются в одном пакете. Если я попытаюсь запустить оператор SELECT сам по себе , , он не будет работать :

SQL Server понятия не имеет, что такое @Products .

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

Дополнительные сведения о пакетах см. в руководстве по SQL Server GO:

SQL Server GO: объяснение с примерами

2) Откаты не отменяют работу, выполненную с табличной переменной

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

В качестве очень простого примера давайте вставим строки в нашу табличную переменную внутри транзакции , а затем немедленно ОТМЕНИМ транзакцию:

Из оператора SELECT в конце мы видим, что данные все еще находятся в таблице. переменная . Работа, проделанная с табличной переменной, была просто не отменена  откатом.

Это еще одна вещь, которая верна для любой переменной .

3) Табличные переменные ведут себя так же, как обычные таблицы

Я уже говорил это раньше, но стоит повторить. Многое из того, что вы можете сделать с обычной таблицей, можно сделать и с табличной переменной. Вы можете делать такие вещи, как:

  • Добавить индексы
  • Создать ограничения
  • Использовать свойство IDENTITY
  • Добавить первичные ключи
  • и т.