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

Откуда:
Сообщений: 165
Всем привет,
столкнулся с интересным (для меня по крайней мере) случаем:
вставка большого числа строк (от 60 000 до 800 000) работает быстрее, если вставлять в @table. Если вставлять в #table все делается на порядок медленней.

Процедура примерно такого плана:
+
insert into @t (800 000 rows)
insert into @t (800 000 rows)
insert into @t (800 000 rows)
insert into @t (800 000 rows)
insert into @t (800 000 rows)

update @t
update @t
update @t
update @t

insert into dbo.table 
select * from @t

Индексы и FK не используются. Нет даже РК.

Я всегда считал, что на таких объемах всегда лучше использовать #table.
Тут вспоминается реплика Д-ра Ватсона из соответствующего сериала - "Холмс, но КАААК?" ©

Причина явно в разных планах выполнения. Но тогда что действительно лучше в данном случае?

Разъясните пожалуйста.

MS SQL 2012

P.S. На форуме есть похожие темы, но там обратная ситуация - # быстрее чем @.
Плюс недавно откопал такую статью: https://support2.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k, она меня запутала еще больше.
3 июн 15, 14:13    [17725442]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
0-0
Guest
whitebeast,

табличная переменная это память
временная таблица - физическая таблица в tempdb, которая храниться на диске

Что тут нелогичного?
Насколько быстрее работает то?
3 июн 15, 14:22    [17725498]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
whitebeast
Member

Откуда:
Сообщений: 165
0-0,

на 800 000 разница 2.5 раза.
0-0
табличная переменная это память

не соглашусь. Точно так же хранится в tempdb.
3 июн 15, 14:30    [17725534]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10436
whitebeast
0-0,

на 800 000 разница 2.5 раза.
0-0
табличная переменная это память

не соглашусь. Точно так же хранится в tempdb.


да ну?
3 июн 15, 14:41    [17725611]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
0-0
Guest
whitebeast
0-0,

на 800 000 разница 2.5 раза.
0-0
табличная переменная это память

не соглашусь. Точно так же хранится в tempdb.


А где ваши доказательства?
3 июн 15, 14:43    [17725633]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19449
whitebeast
0-0
табличная переменная это память

не соглашусь. Точно так же хранится в tempdb.

Переменная не модет храниться на диске (если не считать свопа, конечно). И то, что она табличная, ничего не меняет.
3 июн 15, 14:45    [17725649]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
o-o
Guest
граждане, не ссорьтесь, конечно же @table хранится в tempdb:
declare @t table (xxx int);
insert into @t values (1);

select object_name(object_id, DB_ID('tempdb')), *
from tempdb.sys.columns
where name = 'xxx';


К сообщению приложен файл. Размер - 48Kb
3 июн 15, 14:47    [17725657]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2399
whitebeast
0-0,

на 800 000 разница 2.5 раза.
0-0
табличная переменная это память

не соглашусь. Точно так же хранится в tempdb.


А вы попробуйте влить в вашу переменку чуть поболее данных, так чтобы в памяти не поместилось, тогда и увидите что к чему.
3 июн 15, 14:47    [17725662]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
whitebeast
Member

Откуда:
Сообщений: 165
0-0
А где ваши доказательства?

А Ваши где? :)

https://support2.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k
MS
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).


Все таки я бы хотел узнать ответ на поставленный вопрос:
whitebeast
Причина явно в разных планах выполнения. Но тогда что действительно лучше в данном случае?
3 июн 15, 14:47    [17725663]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
o-o
Guest
Akina
whitebeast
пропущено...

не соглашусь. Точно так же хранится в tempdb.

Переменная не модет храниться на диске (если не считать свопа, конечно). И то, что она табличная, ничего не меняет.

ну так и таблица (временная) не обязана на диск попадать (если не считать свопа, конечно)
короче, все лежит в памяти,
но наверное, если очень постараться, то и на диск можно отправить все, что угодно
3 июн 15, 14:51    [17725702]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
o-o
Guest
или сами воспроизводите, или не знаю что.
у меня планы одинаковые и время тоже
вставляю 1000000 строк.

К сообщению приложен файл. Размер - 63Kb
3 июн 15, 15:01    [17725786]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
Albatross
Member

Откуда:
Сообщений: 997
o-o,

А если например, сервер думал, что переменная или временная таблица мало места займет и создал её в памяти. А тут опа- лярд строк.
И что он - будет из памяти перемещать на диск прямо во время инсерта?
тогда будет большая задержка в какой-то момент.
3 июн 15, 15:02    [17725794]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29246
Akina
whitebeast
не соглашусь. Точно так же хранится в tempdb.

Переменная не модет храниться на диске (если не считать свопа, конечно). И то, что она табличная, ничего не меняет.
С чего вы взяли? они одинаково хранятся; разница есть в обработке, но это объекты в tempdb, их можно просто в списке объектов найти.
3 июн 15, 15:03    [17725808]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
Albatross
Member

Откуда:
Сообщений: 997
Albatross
o-o,

А если например, сервер думал, что переменная или временная таблица мало места займет и создал её в памяти. А тут опа- лярд строк.
И что он - будет из памяти перемещать на диск прямо во время инсерта?
тогда будет большая задержка в какой-то момент.

А хотя нет - написано же - Data Cache.
Т.е. всё как обычные таблицы.
3 июн 15, 15:05    [17725829]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
whitebeast
Member

Откуда:
Сообщений: 165
Чуть позже скину планы выполнения.
Проблема в том, что они малочитаемы, очень много таблиц. Вложенные вью и т.п.
Удобней будет в текстовом виде или графическом?
3 июн 15, 15:10    [17725864]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
whitebeast
Member

Откуда:
Сообщений: 165
В приложении два плана выполнения.
В процедуре закомментировал все, кроме первой вставки в таблицы.
Т.е. происходит только объявление таблицы/переменной и вставка.
больше ничего.

1. Почему разные планы выполнения?
2. Имеет ли смысл дальше ковырять, для того чтобы использовать # таблицы? Я уже сомневаюсь в этом, но очень интересно понять причину.

К сообщению приложен файл (pack.zip - 118Kb) cкачать
3 июн 15, 15:20    [17725917]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 3961
С InMemory сравните.
3 июн 15, 15:26    [17725955]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
whitebeast
Member

Откуда:
Сообщений: 165
a_voronin,
Если не ошибаюсь InMemory появилась только в 2014 версии. У меня же, как я писал, 2012 версия.
3 июн 15, 15:31    [17725991]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
whitebeast
Member

Откуда:
Сообщений: 165
Еще раз - забудем про то, где хранятся # и @. В моем случае они хранятся в tempdb.

Почему разные планы выполнения? SELECT ведь один и тот же.
3 июн 15, 15:34    [17726010]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19449
Albatross
И что он - будет из памяти перемещать на диск прямо во время инсерта?
тогда будет большая задержка в какой-то момент.
Инсерт в эту табличную переменную, в смысле? есссно... если переменная так распухла, что не лезет в память, по-любому она будет сливаться на диск, и именно во время инсерта. Если ты начнёшь в, скажем, ntext наливать второй гигабайт - она тоже в памяти фиг удержится. Прямо во время операции присвоения.

alexeyvg
С чего вы взяли? они одинаково хранятся; разница есть в обработке, но это объекты в tempdb, их можно просто в списке объектов найти.

Вот именно что в обработке. В хранении разницы нет, верно?

Тогда представим, что это обычная переменная. Скалярная, скажем, строковая. Где она? в памяти. Да, в tempdb объект лёг, копия значения, скорее всего, тоже - но переменная в памяти. И используется оттуда. Перестанет помещаться - другое дело, вся или часть ляжет в базу, вся или часть будет выброшена из памяти. Потребуется - подкачается, вся или частично. Кабы она каждый раз с диска тянулась - во было бы тормозилово переменные использовать...
Не вижу оснований к тому, чтобы с табличной переменной было иначе. Копия на диске, а вся или помещающаяся часть для оперативной работы кода - в памяти. В смысле в области памяти с данными программного кода сервера.

А вот временная таблица - она на диске и, если не вымылась по старости, в кэше.

Мне лично так кажется.
3 июн 15, 15:35    [17726017]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29246
Akina
alexeyvg
С чего вы взяли? они одинаково хранятся; разница есть в обработке, но это объекты в tempdb, их можно просто в списке объектов найти.

Вот именно что в обработке. В хранении разницы нет, верно?

Тогда представим, что это обычная переменная. Скалярная, скажем, строковая. Где она? в памяти. Да, в tempdb объект лёг, копия значения, скорее всего, тоже - но переменная в памяти. И используется оттуда. Перестанет помещаться - другое дело, вся или часть ляжет в базу, вся или часть будет выброшена из памяти. Потребуется - подкачается, вся или частично. Кабы она каждый раз с диска тянулась - во было бы тормозилово переменные использовать...
Не вижу оснований к тому, чтобы с табличной переменной было иначе. Копия на диске, а вся или помещающаяся часть для оперативной работы кода - в памяти. В смысле в области памяти с данными программного кода сервера.

А вот временная таблица - она на диске и, если не вымылась по старости, в кэше.
Никакой разницы с временной таблицой.

При записи временной таблицы (или таблицы-переменной) в tempdb она будет храниться в памяти.

Вы что, думаете, что если таблица в tempdb, то она непременно на диске?

Это не так, если памяти достаточно, то таблица так и будет в памяти, а на диск записываться не будет (или будет - в фоне, потом).
Akina
В смысле в области памяти с данными программного кода сервера.
Нет, "в области программного кода", а точнее, в области данных табличная переменная не хранится, в отличие от обычных скалярных переменных.

Она хранится там, где хранятся данные всех таблиц, и постоянных тоже - в страничном кеше.
3 июн 15, 17:38    [17726943]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29246
Akina
Вот именно что в обработке. В хранении разницы нет, верно?
А в обработке - это имеется в виду сбор статистики, компиляции, обработка транзакций, запись в лог - вот тут да, тут уже появляется разница между табличной переменной и временной таблицей.
3 июн 15, 17:40    [17726951]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
iap
Member

Откуда: Москва
Сообщений: 46858
Winnipuh
whitebeast
0-0,

на 800 000 разница 2.5 раза.
пропущено...

не соглашусь. Точно так же хранится в tempdb.


да ну?
Не сомневайтесь! Именно так.
Но если есть возможность, и то, и другое может целиком загружаться в память
3 июн 15, 17:41    [17726960]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
o-o
Guest
iap
Winnipuh
пропущено...
да ну?
Не сомневайтесь! Именно так.
Но если есть возможность, и то, и другое может целиком загружаться в память

не наоборот ли?
и то, и то, в памяти создается, а вот если надо будет, то на диск вывалится.
когда говорят "в tempdb", это значит, на станицах базы tempdb.
но это же все равно в памяти
3 июн 15, 17:53    [17727011]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Табличная переменная находится в tempdb, так же как и временная таблица. И механизмы работы Storage Engine с одной и другой похожие, разумеется, с различием в нюансах работы с транзакционной моделью. В этом смысле и временные таблицы и табличные переменные гораздо ближе к обычным таблицам, чем к скалярным переменным в памяти. В конце-концов, с обычными таблицами, ведь тоде через BPool идет работа, который, суть - память, но никто обычно не говорит, что у нас "таблицы в памяти, но могут быть сброшены на диск".

whitebeast,
Если допишете в запрос с #временной таблицей в конец хинт "option(maxdop 1)", есть подозрение, что ситуация изменится.
3 июн 15, 18:38    [17727210]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
whitebeast
Member

Откуда:
Сообщений: 165
SomewhereSomehow,

благодарю за единственный ответ по теме.
http://dba.stackexchange.com/questions/103157/different-execution-plans-during-insert-at-table-variable-and-temporary-table
вот здесь советовали то же самое. Буду пробовать.
3 июн 15, 18:55    [17727272]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
Изопропил
Member

Откуда:
Сообщений: 31209
Други,

а чё там с транзакциями?

сдаётся мне, что табличной переменной похер на них в отличие от временной таблицы
со всеми вытекающими
3 июн 15, 18:59    [17727286]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 994
Изопропил
Други,

а чё там с транзакциями?

сдаётся мне, что табличной переменной похер на них в отличие от временной таблицы
со всеми вытекающими


похер, но логируется, если память не изменяет, одинаково. fn_dblog может все прояснить. но че-то лень.
3 июн 15, 19:14    [17727327]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
o-o
Guest
SomewhereSomehow
В конце-концов, с обычными таблицами, ведь тоде через BPool идет работа, который, суть - память, но никто обычно не говорит, что у нас "таблицы в памяти, но могут быть сброшены на диск".

ну так обычно никто и не заявляет, что
iap
Но если есть возможность, и то, и другое может целиком загружаться в память

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

такое вот прочтешь и подумаешь: да. берем @table, пишем на диск. но, может и загрузим в память.

мне-то не надо объяснять, что куда *может* отправиться, на диск из памяти или наоборот,
но т.к. *моя* картинка в этой теме на весь экран кричит, где расположена табличная переменная (tempdb)
a это потом интерпретируют как "сперва диск, потом, возможно, память", я буду протестовать и уточнять
3 июн 15, 20:29    [17727475]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 29246
o-o
а временные-то занафига, если только не как "вынужденная мера"?
Ну, может ,что бы поддержать баланс свободного места в страничном кеше?

Впрочем, не знаю алгоритма, может, для временных таблиц есть какие то "веса" при сбросе кеша? Но сам по себе дисковый ИО наблюдать легко при заполнении временной таблицы.
3 июн 15, 22:13    [17727748]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
o-o
Guest
alexeyvg
Но сам по себе дисковый ИО наблюдать легко при заполнении временной таблицы.

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

What does checkpoint do for tempdb?
when a checkpoint occurs for a user database, all dirty pages for that database are flushed to disk (as well as other operations).
This does not happen for tempdb. Tempdb is not recovered in the event of a crash, and so
there is no need to force dirty tempdb pages to disk,
except in the case where the lazywriter process (part of the buffer pool) has to make space for pages from other databases
.
Of course, when you issue a *manual* CHECKPOINT, all the dirty pages are flushed, but for automatic checkpoints they’re not.
You can easily prove this to yourself with a large transaction inserting into a user table on tempdb,
and watch the Databases/Log File(s) Used Size (KB) and Databases/Log Truncations for tempdb,
when you see them go up, check to see if the number of dirty pages in tempdb has reduced – it won’t have.

может, он еще место резервирует, но данные точно не сбрасывает, вот смотрите:
я перезапускаю сервер, темпдб пересоздается, не делаю ровным счетом ничего
и вставляю во временную таблицу сотню мегабайт.
смотрю расклад в памяти по чистым/грязным страницам, ну вот они, мои 12800 грязных страниц.
т.е. отличающихся от того, что на диске.
а на диске что? там НЕ БЫЛО вообще этой таблицы, так что все лежит в памяти.

еще как это видно: #t заполняет мне мгновенно, т.е. за секунду.
а вот если после этого сделать чекпойнт в темпдб, то бедный ноут начинает издавать звуки, греться,
и 10 секунд молотит данные

К сообщению приложен файл. Размер - 20Kb
3 июн 15, 23:24    [17727873]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
o-o
Guest
Изопропил
Други,

а чё там с транзакциями?

сдаётся мне, что табличной переменной похер на них в отличие от временной таблицы
со всеми вытекающими

да все там примерно одинаково,
анализировать уже лень, но вот что ушло в лог, картинкой.
код вот:
--tempdb: checkpoint;

--user_db:
create table #t (id int, col char(8000));
go

insert into #t(id, col)
select n, 'a'
from dbo.Nums
where n <= 12800;
----

--tempdb:
select *
from sys.fn_dblog(null, null);

select Operation, context, COUNT(*) as cnt
from sys.fn_dblog(null, null)
group by Operation, context
order by cnt desc
----
----

--tempdb: checkpoint;

--user_db:
declare @t table (id int, col char(8000));

insert into @t(id, col)
select n, 'a'
from dbo.Nums
where n <= 12800;

--tempdb:
select *
from sys.fn_dblog(null, null);

select Operation, context, COUNT(*) as cnt
from sys.fn_dblog(null, null)
group by Operation, context
order by cnt desc


К сообщению приложен файл. Размер - 46Kb
3 июн 15, 23:49    [17727898]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
Slava_Nik
Member

Откуда: из России
Сообщений: 872
whitebeast
Еще раз - забудем про то, где хранятся # и @. В моем случае они хранятся в tempdb.

Почему разные планы выполнения? SELECT ведь один и тот же.

не было ответа или нет по статистике, планы не смотрел?, тогда добавлю пару копеек.
В табличных переменных после вставки нужно обновить статистику вручную потом делать select, иначе когда создается план, оптимизатор думает, что одна строка, а вы ставили более в итоге план может неоптимальный
В временных таблицах статистика сама обновляется.
4 июн 15, 09:55    [17728605]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
o-o
Guest
так то при чтении.
а зачем ему статистика при вставке о том, куда вставляет?
передумает что ли вставлять?
SELECT-то у него не из того, куда льет
4 июн 15, 10:06    [17728660]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
o-o
Guest
по-моему, единственный вменяемый ответ уже дали: сам ТС ссылкой и SomewhereSomehow.
напишу здесь в явном виде:
inserting into a table variable forces a serial plan
(see the NonParallelPlanReason of CouldNotGenerateValidParallelPlan that appears in the table variable plan,
but not the temp table plan), and this may impact the code path
that the query optimizer takes either generating an initial plan or in some phase of plan optimization.
4 июн 15, 10:10    [17728684]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
whitebeast
Member

Откуда:
Сообщений: 165
Slava_Nik,

в табличных переменных статистики нет вообще. И чтобы избежать случая "в_таблице_1_строка_хотя_на_самом_деле_больше" надо добавлять OPTION (RECOMPILE).
Но все это не относится к теме, т.к. я ясно описал ситуацию - разные планы НЕ ВО ВРЕМЯ ЧТЕНИЯ ИЗ ВРЕМЕННОЙ ТАБЛИЦЫ/ПЕРЕМЕННОЙ, а ВО ВРЕМЯ ВСТАВКИ В ВРЕМЕННУЮ ТАБЛИЦУ/ПЕРЕМЕННУЮ.

А вы тут развели умносрач на тему где лежат данные в памяти или на диске. Да пофиг мне где они лежат.
4 июн 15, 10:48    [17728983]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
PVA
Member

Откуда: Питер
Сообщений: 22
Всегда руководствовался следующим:
Разница между временно таблице и переменной - только в том, что при операция[ с таблицей-переменной не ведется логирование. Поэтому операции с таблицей-переменной - быстрее. Обычно в хранимых процедурах не нужны транзакции, при построении отчетов, например, поэтому это совершенно приемлемо.

А что касается сравнения планов выполнения, так нужно строку таблице сделать побольше. Больше длина строки => больше записи в лог => больше выигрыш использования таблицы-переменной.

Ну и еще - имею привычку удалять то, что создано после использования. Поэтому стараюсь добавлять DROP TABLE #ИМЯ в случае использования временной таблицы. А с переменной этого делать не нужно.

Да и "@" смотрится намного симпатичнее, чем "#" )))))))))))
4 июн 15, 11:05    [17729091]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
o-o
Guest
whitebeast,

вы не обижайтесь так, это не совсем срач, ваша тема -- повод для беседы, каждый выясняет то, что его интересует :)
кстати, вы все еще ищете блог sqlcmd?
надо было в нашей ветке искать, а если старая ссылка протухла, можно снова выложить.

вас чем ответ с того форума не устроил?
я вот соглашусь с тем товарищем, на перебор всех планов с вашей кучей всего в запросе
дается ограниченное время, поэтому "отправная точка" имеет значение.
и в вашем случае ей оказалась табличная переменная.
вы вот там хотите MAXDOP 1 к обоим запросам прикрутить,
а вообще-то ведь хватит только к тому, что со временной таблицей.
т.к. если ответ именно такой, что быстрее оказался "непараллельный" план,
то достаточно спровоцировать его выбор и в случае временных таблиц.
т.е. мораль: не в них дело, @table vs #table, а в том, что он с разного начал поиск.
потому вам и советовали еще видоизменить запрос,
глядишь, он с еще нового плана начнет, и еще лучшем закончит
4 июн 15, 11:07    [17729101]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
Glory
Member

Откуда:
Сообщений: 104764
PVA
Разница между временно таблице и переменной - только в том, что при операция[ с таблицей-переменной не ведется логирование.Поэтому операции с таблицей-переменной - быстрее.

Все там ведется. Даже вон картинка сверху имеется для подтверждения

PVA
А что касается сравнения планов выполнения, так нужно строку таблице сделать побольше. Больше длина строки => больше записи в лог => больше выигрыш использования таблицы-переменной.

Мда.

PVA
Ну и еще - имею привычку удалять то, что создано после использования. Поэтому стараюсь добавлять DROP TABLE #ИМЯ в случае использования временной таблицы. А с переменной этого делать не нужно.

Особенно не нужно удалять таблицчную переменную в виду полного отсутствия команды ее удаления
А про область видимости и время жизни временных таблиц все написано в хелпе
4 июн 15, 11:12    [17729136]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
o-o
Guest
PVA
при операция[ с таблицей-переменной не ведется логирование. Поэтому операции с таблицей-переменной - быстрее.

вы хоть бы проверяли, что пишете, или уточняли, какая именно разница в логировании.
вон у меня вставка и в @t, и в #t, что-то не замечаю "нелогирования" в случае табличной переменной.
нетранзакционность это не синоним нелогирования
4 июн 15, 11:13    [17729149]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
PVA
Member

Откуда: Питер
Сообщений: 22
o-o
PVA
при операция[ с таблицей-переменной не ведется логирование. Поэтому операции с таблицей-переменной - быстрее.

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


Обычно задача гораздо сложнее. Сначала вставка, потом UPDATE (не всегда можно обойтись без курсора), или групповой UPDATE ... FROM ...
С переменной все в комплексе работает быстрее.
Хотя, не скрою, временные таблицы все же использую. Например, для передачи массива строк в другую процедуру, вызываемую из данной.

А ставить эксперименты, чтобы запостить здесь отчет - извините, времени нет.
4 июн 15, 11:23    [17729205]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
Glory
Member

Откуда:
Сообщений: 104764
PVA
Обычно задача гораздо сложнее. Сначала вставка, потом UPDATE (не всегда можно обойтись без курсора), или групповой UPDATE ... FROM ...
С переменной все в комплексе работает быстрее.

Работает быстрее лучший план, а не таблица или переменная

PVA
А ставить эксперименты, чтобы запостить здесь отчет - извините, времени нет.

Да запостили уже за вас - поднимите глаза
4 июн 15, 11:29    [17729244]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
Slava_Nik
Member

Откуда: из России
Сообщений: 872
whitebeast
Slava_Nik,

в табличных переменных статистики нет вообще. И чтобы избежать случая "в_таблице_1_строка_хотя_на_самом_деле_больше" надо добавлять OPTION (RECOMPILE).
Но все это не относится к теме, т.к. я ясно описал ситуацию - разные планы НЕ ВО ВРЕМЯ ЧТЕНИЯ ИЗ ВРЕМЕННОЙ ТАБЛИЦЫ/ПЕРЕМЕННОЙ, а ВО ВРЕМЯ ВСТАВКИ В ВРЕМЕННУЮ ТАБЛИЦУ/ПЕРЕМЕННУЮ.

А вы тут развели умносрач на тему где лежат данные в памяти или на диске. Да пофиг мне где они лежат.

две статистики есть - на первичный ключ и на уникальное поле.
4 июн 15, 11:33    [17729272]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
whitebeast
Member

Откуда:
Сообщений: 165
o-o
whitebeast,

вы не обижайтесь так, это не совсем срач, ваша тема -- повод для беседы, каждый выясняет то, что его интересует :)
кстати, вы все еще ищете блог sqlcmd?
надо было в нашей ветке искать, а если старая ссылка протухла, можно снова выложить.


Не обижаюсь. Просто сравниваю с тем же http://dba.stackexchange.com/, где задал конкретный вопрос и получил конкретный ответ.
А не размышлизмы на смежную тему. Мне мое время дорого :)

Насчет блога sqlcmd - очень круто, что он уже есть на этом сайте :) Но я все равно на всякий случай выдрал копию из т.н. интернет-кэша. Буду знать, спасибо :)

А запрос я видоизменить не могу к сожалению. Я могу только сказать, что вот тут и тут можно сделать так и так. Предложение разбить запрос на множество мелких было проигнорировано разработчиком. Ну и пусть :)
4 июн 15, 12:06    [17729518]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
whitebeast
Member

Откуда:
Сообщений: 165
Кстати тесты показали, что MAXDOP для @ не дает никакого выигрыша в производительности. А вот для # - прирост в 2 раза. с 6 минут до 3. При этом чуточку быстрее чем @ :)
4 июн 15, 12:10    [17729545]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
whitebeast
Member

Откуда:
Сообщений: 165
Slava_Nik,
действительно. Но только если их создавать в переменной:) В моем случае этого не происходило. Обычная временная переменная, без PK/UQ constraints.
4 июн 15, 12:11    [17729557]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
o-o
Guest
whitebeast
Кстати тесты показали, что MAXDOP для @ не дает никакого выигрыша в производительности. А вот для # - прирост в 2 раза. с 6 минут до 3. При этом чуточку быстрее чем @ :)

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

поэтому вам и советовали добавить MAXDOP в случае временных таблиц.
просто чтобы заставить его снова выбрать последовательный план.

не в сторону сравнения @t vs #t надо копать,
а "как его заставить выбрать другой план"
4 июн 15, 12:22    [17729624]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10436
o-o
whitebeast
Кстати тесты показали, что MAXDOP для @ не дает никакого выигрыша в производительности. А вот для # - прирост в 2 раза. с 6 минут до 3. При этом чуточку быстрее чем @ :)

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

поэтому вам и советовали добавить MAXDOP в случае временных таблиц.
просто чтобы заставить его снова выбрать последовательный план.

не в сторону сравнения @t vs #t надо копать,
а "как его заставить выбрать другой план"


лайкнул
4 июн 15, 12:31    [17729673]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
whitebeast
А вот для # - прирост в 2 раза. с 6 минут до 3. При этом чуточку быстрее чем @ :)

Скорее всего и план стал больше походить на тот, что с табличной переменной.
Процесс оптимизации идет по стадиям: search 0 -> (memo) -> search 1 -> (memo) -> search 1(parallel) -> (memo) -> search 2. Если есть возможность параллельного плана, он будет рассмотрен на стадии search 1 (parallel). Это не значит, что он будет выбран, но будет рассмотрен. Результаты каждой из стадий хранятся в специальной структуре Memo. Чтобы работа предыдущей стадии учитывалась, на вход следующей подается то что находится в Memo после предыдущей стадии. Когда-то давно описывал процесс тут Оптимизатор (ч.4): Optimization: Full Optimization: Search 1.

Оба запроса доходят до последней стадии search 2, но на входе после предыдущей стадии имеют разное поле вариантов. Тот, что с табличной переменной имеет поле вариантов после стадии search 1, тот что с табличной переменной search 1(parallel). В результате выбираются разные планы.
Картинка с другого сайта.

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

Чтобы сделать это более предсказуемым, лучше разбить запрос на части. Хотя, я так понял, для вас это не вариант. Тем не менее, лучше это сделать, или хотя бы взять это на заметку, все-таки 33 соединения в запросе...

Удачи.
4 июн 15, 12:35    [17729700]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Опечатался
SomewhereSomehow
Тот, что с табличной переменной имеет поле вариантов после стадии search 1, тот что с табличной переменной временной таблицей search 1(parallel).
4 июн 15, 12:39    [17729733]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
invm
Member

Откуда: Москва
Сообщений: 8797
Как посмореть транзакцию на табличную переменную (а то некоторые упертые личности могут счесть картинку не показательной):
use tempdb;

declare @t table ([@t_column1] int);

select
 object_name(object_id)
from
 sys.columns
where
 name = N'@t_column1';

declare @c nvarchar(30), @x nvarchar(30);
select @c = max([Current LSN]) from sys.fn_dblog(null, null);

insert into @t values (1);
select @x = [Transaction ID] from sys.fn_dblog(null, null) where [Current LSN] > @c and SPID = @@spid and [Transaction Name] = N'TVQuery' and Operation = N'LOP_BEGIN_XACT';

select * from sys.fn_dblog(null, null) where [Transaction ID] = @x;
4 июн 15, 12:41    [17729751]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
PVA
Member

Откуда: Питер
Сообщений: 22
Еще информация по теме
http://www.sqlservercentral.com/articles/Temporary Tables/66720/
4 июн 15, 13:38    [17730130]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
Александр Бердышев
Member

Откуда: Санкт-Петербург
Сообщений: 276
Тема конечно холиварная.

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

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

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

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

В остальных случаях лучше использовать временные таблицы.
8 окт 19, 16:47    [21989661]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6612
Александр Бердышев,

вот прям новость, но с какого-то... 2012-14 статистика внутри табличных переменных уже адекватна
8 окт 19, 18:03    [21989741]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
fkthat
Member

Откуда:
Сообщений: 1340
Табличная переменная, в отличии от временной таблицы не ведет статистику и не участвует в явной транзакции (если есть явнвя транзакция). Возможно дело в этом.
8 окт 19, 18:48    [21989782]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
0wl
Member

Откуда:
Сообщений: 51
TaPaK,

Не совсем. Начиная с 2014 сиквел считает, что в табличной переменной не одна строка, а 100. Иногда действительно выручает. Плюс, появился флаг 2453, который приводит к перекомпиляции плана с учетом данных от предыдущего выполнения запроса.

В 2019 обещают светлое будущее и более адекватную статистику для переменных.
8 окт 19, 18:49    [21989785]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6612
0wl
TaPaK,

Не совсем. Начиная с 2014 сиквел считает, что в табличной переменной не одна строка, а 100. Иногда действительно выручает. Плюс, появился флаг 2453, который приводит к перекомпиляции плана с учетом данных от предыдущего выполнения запроса.

В 2019 обещают светлое будущее и более адекватную статистику для переменных.

Что за фантазии всем пунктам
8 окт 19, 22:19    [21989906]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 32231
Блог
TaPaK
Александр Бердышев,

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


Неужели ms нам врет? )


https://docs.microsoft.com/en-us/sql/t-sql/data-types/table-transact-sql?view=sql-server-2017
Table variables don't have distribution statistics
8 окт 19, 22:47    [21989922]     Ответить | Цитировать Сообщить модератору
 Re: почему @табличная переменная работает быстрей чем #временная таблица на INSERT?  [new]
0wl
Member

Откуда:
Сообщений: 51
TaPaK,
Да [url="https://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server"]фантазеров [/url][url="https://www.mssqltips.com/sqlservertip/5662/table-variable-deferred-compilation-in-sql-server/ "]всяких [/url]начитался

Про 100 строк -- каюсь, не нашёл (хотя в планах, помню, видел. Но пруфов под рукой нет, спишем на усталость зрения)

Но факт в том, что главное, почему "в 2012-2014" статистика (точнее предполагаемое число строк) может быть "адекватна" -- это флаг 2453
9 окт 19, 10:46    [21990153]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2 3      [все]
Все форумы / Microsoft SQL Server Ответить