Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Табличные переменные vs временные таблицы  [new]
Новичек SQL Server
Guest

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


Исходя из подчеркнутого, работа с табличными переменными должны быть быстрее, по сравнению с временными таблицами. Можно ли утверждать, что они предпочтительнее временных таблиц, для хранения относительно небольших по размерам промежуточных результатов? Или при их использовании - есть какие-то "подводные камни"?
26 июл 08, 18:20    [5989638]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные vs временные таблицы  [new]
daw
Member

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

откуда цитируете?


Q4: Are table variables memory-only structures that are assured better performance as compared to temporary or permanent tables,
because they are maintained in a database that resides on the physical disk?

A4: A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has
to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory
is available, both table variables and temporary tables are created and processed while in memory (data cache).

http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k

Posted via ActualForum NNTP Server 1.4

26 июл 08, 18:33    [5989656]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные vs временные таблицы  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
daw
откуда цитируете?

В цитате всё более-менее правильно, просто баланс перекошен ("избыток данных будет буферизирован на диск в базу данных tempdb")

автор
Можно ли утверждать, что они предпочтительнее временных таблиц

В общем случае - нет.
26 июл 08, 18:35    [5989660]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные vs временные таблицы  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
>В цитате всё более-менее правильно, просто баланс перекошен ("избыток данных будет буферизирован на диск в базу данных tempdb")

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

Posted via ActualForum NNTP Server 1.4

26 июл 08, 18:41    [5989666]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные vs временные таблицы  [new]
Новичек SQL Server
Guest
Это цитата из книги - "SQL Server 2005. Реализация и обслуживание. Экзамен 70-431. Стр.97". Которое позиционируется как - "Официальное пособие по самоподготовке. Учебные курс Microsoft"...
26 июл 08, 18:55    [5989670]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные vs временные таблицы  [new]
Новичек SQL Server
Guest
Вот из этой книги
26 июл 08, 18:59    [5989671]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные vs временные таблицы  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
ну, если что, вот вам еще:

Inside Microsoft SQL Server 2005 - T-SQL Programming

tempdb
To dispel what probably is the most widespread myth involving table variables, let me state that they do have physical
representation in tempdb, very similar to temporary tables. As proof, run the following code that shows which temporary tables
currently exist in tempdb by querying metadata info, creates a table variable, and queries metadata info again:

SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%#%';
GO
DECLARE @T TABLE(col1 INT);
INSERT INTO @T VALUES(1);
SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%#%';

When I ran this code, the first batch returned no output, while the second returned #0CBAE877, which is the name of the temporary
table in tempdb that represents the table variable @T. Of course, you will probably get a different name when you run this code.
But the point is to show that there is a hidden temporary table created behind the scenes. Just like temporary tables, a table
variable's pages will reside in cache when the table is small enough and when SQL Server has enough memory to spare. So the
discussion about aspects of working with temporary tables with regard to tempdb applies to table variables as well.


Posted via ActualForum NNTP Server 1.4

26 июл 08, 19:28    [5989699]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные vs временные таблицы  [new]
Новичек SQL Server
Guest
2daw
Да, действительно, из приведенного Вами кода, видно, что даже при добавлении всего лишь одной строки в табличную переменную, она также "отмечается" в базе tempdb.

Выходит, что в плане скорости доступа к данным, между табличной переменной и временной таблицей разницы нет никакой, так как, что первая, что вторая для своего функционирования используют базу tempdb...
26 июл 08, 20:18    [5989757]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные vs временные таблицы  [new]
Новичек SQL Server
Guest
Да, вот еще.


Q4: Are table variables memory-only structures that are assured better performance as compared to temporary or permanent tables,
because they are maintained in a database that resides on the physical disk?

A4: A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).



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

Вот у меня на компе с 2Гб оперативной памяти и версией сервера - Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) , все равно использовалась база tempdb, при прогоне кода с вставкой одной строки в табличную переменную, который Вы привели. Здесь тоже непонятно - почему? Что для вставки одной строки с одним целочисленным полем в табличную переменную, было не достаточно памяти?
26 июл 08, 20:57    [5989796]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные vs временные таблицы  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
Новичек SQL Server
2daw
Да, действительно, из приведенного Вами кода, видно, что даже при добавлении всего лишь одной строки в табличную переменную, она также "отмечается" в базе tempdb.

Из приведенного кода не видно, что переменная отмечается "при добавление строки", а не при декларации.

Следовательно, необходимо переосмыслить вопрос "Что для вставки одной строки с одним целочисленным полем в табличную переменную, было не достаточно памяти?".
26 июл 08, 23:11    [5989919]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные vs временные таблицы  [new]
Hallween
Member

Откуда: Москва
Сообщений: 170
Вот здесь есть довольно неплохая, на мой взгляд, статья по теме. И трудно не согласиться с выводом, приведенным в её конце:

The take home message from the thread: test, test, test. (And don't believe everything you read - perform your own tests to verify other's conclusions/assertions.)
26 июл 08, 23:38    [5989951]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные vs временные таблицы  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5503
Блог
Новичек SQL Server
Что для вставки одной строки с одним целочисленным полем в табличную переменную, было не достаточно памяти?
Дело вовсе не в недостатке памяти. :)
Есть некая разница между тем, что для табличной переменной резервируется место в tempdb и тем, происходит или нет физическая запись (сброс страниц) табличной переменной на диск.
Поскольку табличные переменные ведут себя точно так же, как временные (да и обычные - тоже) таблицы, серверу, конечно нужно резервировать место для них в пространстве базы tempdb.

DECLARE @T TABLE(col1 INT PRIMARY KEY);
SELECT * FROM tempdb.dbo.sysindexes
WHERE name LIKE '%#%';
INSERT INTO @T VALUES(1);
SELECT * FROM tempdb.dbo.sysindexes
WHERE name LIKE '%#%';
Судя по этим результатам, место в tempdb резервируется действительно только в момент появления хотя бы одной записи.
28 июл 08, 01:58    [5991225]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные vs временные таблицы  [new]
Новичек SQL Server
Guest
DeColo®es
Новичек SQL Server
Что для вставки одной строки с одним целочисленным полем в табличную переменную, было не достаточно памяти?
Дело вовсе не в недостатке памяти. :)
Есть некая разница между тем, что для табличной переменной резервируется место в tempdb и тем, происходит или нет физическая запись (сброс страниц) табличной переменной на диск.
Поскольку табличные переменные ведут себя точно так же, как временные (да и обычные - тоже) таблицы, серверу, конечно нужно резервировать место для них в пространстве базы tempdb.

DECLARE @T TABLE(col1 INT PRIMARY KEY);
SELECT * FROM tempdb.dbo.sysindexes
WHERE name LIKE '%#%';
INSERT INTO @T VALUES(1);
SELECT * FROM tempdb.dbo.sysindexes
WHERE name LIKE '%#%';
Судя по этим результатам, место в tempdb резервируется действительно только в момент появления хотя бы одной записи.


Скорее всего, я не конца, что-то понимаю....
Вот, если разбить Ваш код на 3 части и выполнить только 2 части. Собственно вставку самой строки не выполнять, то видно, что и без вставки строки резервируется место в базе tempdb. Или я не правильно трактую результаты запроса?

К сообщению приложен файл. Размер - 0Kb
28 июл 08, 12:31    [5992543]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные vs временные таблицы  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
>Скорее всего, я не конца, что-то понимаю....
>Вот, если разбить Ваш код на 3 части и выполнить только 2 части. Собственно вставку самой строки
>не выполнять, то видно, что и без вставки строки резервируется место в базе tempdb.
>Или я не правильно трактую результаты запроса?

без вставки только метаданные о таблице заносятся в системные таблицы.
а непосредственно резервируется место только после вставки.
обратите внимание на столбцы first и FirstIAM до и после вставки.

а по поводу того, что строка в sysindexes оказывается даже "до" объявления - так
это особенность declare такая. как говорили на этом форуме "это вовсе не оператор".


Posted via ActualForum NNTP Server 1.4

28 июл 08, 12:57    [5992722]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные vs временные таблицы  [new]
Новичек SQL Server
Guest
daw

без вставки только метаданные о таблице заносятся в системные таблицы.
а непосредственно резервируется место только после вставки.
обратите внимание на столбцы first и FirstIAM до и после вставки.

а по поводу того, что строка в sysindexes оказывается даже "до" объявления - так
это особенность declare такая. как говорили на этом форуме "это вовсе не оператор".


Posted via ActualForum NNTP Server 1.4


Если я правильно понял, то вывод такой:
Табличная переменная также как и временная таблица - используют базу tempdb. И уже при своей декларации - заносит данные о себе в системные таблицы, а в базе - tempdb создает соответствующую таблицу. А собственно само место под эту таблицу отводится в момент вставки в них строк.

Вот еще такой вопрос.
Сама база tempdb - находится в оперативной памяти, а при ее (оперативной памяти) нехватке будет частично сбрасывать себя на диск или как?
28 июл 08, 13:50    [5993048]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные vs временные таблицы  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
>Сама база tempdb - находится в оперативной памяти, а при ее (оперативной памяти) нехватке
>будет частично сбрасывать себя на диск или как?

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

Posted via ActualForum NNTP Server 1.4

28 июл 08, 14:36    [5993379]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные vs временные таблицы  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
DeColo®es
Поскольку табличные переменные ведут себя точно так же, как временные (да и обычные - тоже) таблицы, серверу, конечно нужно резервировать место для них в пространстве базы tempdb.
Да не точно также:
					SELECT name FROM tempdb.sys.objects
DECLARE @T TABLE(col1 INT PRIMARY KEY)	SELECT name FROM tempdb.sys.objects
INSERT INTO @T VALUES(1)		SELECT name FROM tempdb.sys.objects
CREATE TABLE #T (col1 INT PRIMARY KEY)	SELECT name FROM tempdb.sys.objects
INSERT INTO #T VALUES(1)		SELECT name FROM tempdb.sys.objects
DROP TABLE #T				SELECT name FROM tempdb.sys.objects
					SELECT @@Version
Результат:
name

name

name

name
#T__________________________________________________________________________________________________________________000000000106
PK__#T________________79B7E1E4

name
#T__________________________________________________________________________________________________________________000000000106
PK__#T________________79B7E1E4

name

Microsoft SQL Server 2005 - 9.00.3159.00 (Intel X86) 
	Mar 23 2007 16:15:11 
	Copyright (c) 1988-2005 Microsoft Corporation
	Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Если на табличные переменные не действует транзакционность, то это уже что-то значит (лога жалко). Не говоря ещё о том что разные результаты производительности (зависит от машины и состояния окружения)
Так что писать желательней переменные, и если грабли, то написать темповые, потом сравнить (и потом выбросить темповые - ибо практически не лучше).

DeColo®es, навязывает табличные (ну не навязывает, но немног завышает), т.к. процедурник - предпочитает процедуры вместо техже триггеров, вьюх, табличных функций. И при этом ему намного чаще требуется необходимость в тэмповых, например для передачи промежуточных табличных данных дальше по дереву вызовов процэдур. (Во всяком случае складывается такое впечатление)
28 июл 08, 20:18    [5995515]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные vs временные таблицы  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
Mnior> Результат:

вы хотите сказать, что табличной переменной вы в tempdb.sys.objects не видите?


Posted via ActualForum NNTP Server 1.4

28 июл 08, 20:31    [5995538]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные vs временные таблицы  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5503
Блог
Mnior
Если на табличные переменные не действует транзакционность, то это уже что-то значит (лога жалко).
Что, простите не действует?
Если уж речь про транзакции, то разница в поведении не в том, что в лог не пишутся изменения, а в том, что они не читаются при откате транзакции.

Mnior
Не говоря ещё о том что разные результаты производительности (зависит от машины и состояния окружения)
Результаты чего? У производительности не может быть результатов - это характеристика, а не процесс, чтобы их иметь.
Разная производительность при использовании временных таблиц и табличных переменных связана главным образом с разными планами запросов, которые строит оптимизатор. В основном различия в том, что сервер не считает статистику на табличных переменных даже если на них построен PK или UNIQUE ограничение.
Оптимизатор "считает", что на любое обращение к табличной переменной вернется одна запись.

Mnior
Так что писать желательней переменные, и если грабли, то написать темповые, потом сравнить (и потом выбросить темповые - ибо практически не лучше).
У переменных и временных таблиц разное поведение и разные функциональные возможности. И более высокая скорость кода с использованием табличных переменных в большОм проценте случаев связана не с отсутствием "транзакционности" и прочих мифических животных, а с рекомпиляциями, возникающих в результате (неимелого) применения временных таблиц.

Mnior
DeColo®es, навязывает табличные (ну не навязывает, но немног завышает), т.к. процедурник - предпочитает процедуры вместо техже триггеров, вьюх, табличных функций.
Хотя бы про меня не фантазируйте, ОК? Особенно забавно про передачу чего-то куда-то... Я об этом вроде на форуме не писал никогда. Вы хоть видели когда-нибудь мой код, чтобы рассуждать о том, как и что я применяю? И откуда домыслы про вьюхи и функции?
28 июл 08, 23:08    [5995814]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные vs временные таблицы  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
daw
Mnior> Результат:
вы хотите сказать, что табличной переменной вы в tempdb.sys.objects не видите?
Да!
Более того, и ранее неоднократно запускал (пример) - ни разу не видел!
Надо вот попробывать залить несколько лимонов строк ... увесистых. Ща буду пробывать ...
29 июл 08, 09:04    [5996377]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные vs временные таблицы  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
>Да!
>Более того, и ранее неоднократно запускал (пример) - ни разу не видел!
>Надо вот попробывать залить несколько лимонов строк ... увесистых. Ща буду пробывать ...

странно. а как, все-таки, пробуете-то? попробуйте так, как Ицик показывает:

https://www.sql.ru/forum/actualthread.aspx?tid=580525#5989699

должна найтись. можно даже не вставлять в нее ничего.

Posted via ActualForum NNTP Server 1.4

29 июл 08, 09:08    [5996387]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные vs временные таблицы  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
>Более того, и ранее неоднократно запускал (пример) - ни разу не видел!

а, кстати, прав точно хватает? под sysadmin-ом для гарантии попробуйте выполнить.

Posted via ActualForum NNTP Server 1.4

29 июл 08, 09:32    [5996512]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные vs временные таблицы  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
DeColo®es
Если уж речь про транзакции, то разница в поведении не в том, что в лог не пишутся изменения, а в том, что они не читаются при откате транзакции.
А моно по подробнее. Ну пжалуста. Вы видимо можете указать как это увидить?
DeColo®es
Разная производительность при использовании временных таблиц и табличных переменных связана главным образом с разными планами запросов, которые строит оптимизатор.
Не придирайтесь, вы вполне поняли о чём я. Всё спокойны как слоны.
DeColo®es
В основном различия в том, что сервер не считает статистику на табличных переменных даже если на них построен PK или UNIQUE ограничение.

Оптимизатор "считает", что на любое обращение к табличной переменной вернется одна запись.

Серьёзно? Видимо это у меня сервер глючный и показвает более не менее актуальное количество строк на каждом этапе плана. По вашей логике hash и merge практически невероятно получить.

DeColo®es
а с рекомпиляциями, возникающих в результате (неумелого) применения временных таблиц.
Возможно, возможно. Про тесты, не затрагивающие рекомпиляцию, и показываюшие одинаковые планы запросов и разность в показателях - можно и забыть. Главное что было сказано о ещё некоторых недостатках.

DeColo®es
Особенно забавно про передачу чего-то куда-то... Я об этом вроде на форуме не писал никогда. Вы хоть видели когда-нибудь мой код, чтобы рассуждать о том, как и что я применяю? И откуда домыслы про вьюхи и функции?
А я к вам это не приписывал. Я лишь сказал, то от чего вы и счас не отреклись и чего неоднократно писали - про процедурность и вьюхи с триггерами. Остальное это лишь результат того или иного использования процедур. Заметьте, что имейте вы хоть 7 пядей во лбу (на то вы и гуру) - от этого не будут все писать правильно. А этот форум отлично показывают то о чём я писал выше.
Неуж-то вы скажете что передача табличных данных посредством темповых таблиц - зло?! Нет. Тото же.

Так что не надо интерпретировать мои слова как "фантазии про вас" (ориетация у меня нормальная, не беспокойтесь :) ) - не льстите себе это вам не нужно. Значёк "гуру" у вас никто не отбирает. ;)
29 июл 08, 10:17    [5996779]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные vs временные таблицы  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
daw
а, кстати, прав точно хватает? под sysadmin-ом для гарантии попробуйте выполнить.
Ща всё попробуем ...
29 июл 08, 10:18    [5996788]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные vs временные таблицы  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
>>Если уж речь про транзакции, то разница в поведении не в том, что в лог не пишутся изменения, а в том, что они не читаются при откате транзакции.
>А моно по подробнее. Ну пжалуста. Вы видимо можете указать как это увидить?

хм. да с помощью того же dbcc log увидеть можно. что пишутся, и как именно, в смысле:

declare @t table (c varchar(100))

dbcc log (2, 2)
begin tran
insert into @t values ('qqqqqqqqqq')
insert into @t values ('aaaaaaaaaa')
rollback tran
dbcc log (2, 2)

Posted via ActualForum NNTP Server 1.4

29 июл 08, 10:31    [5996896]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить