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

Откуда: Київ
Сообщений: 10428
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

Откуда: Зеленоград, Москва, Россия
Сообщений: 20974
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

Откуда: Питер
Сообщений: 2423
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

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

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

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

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

Откуда:
Сообщений: 999
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

Откуда: Москва
Сообщений: 4805
 		С 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Откуда: Москва
Сообщений: 47052
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]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl→      все
Все форумы / Microsoft SQL Server Ответить