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

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

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

сдаётся мне, что табличной переменной похер на них в отличие от временной таблицы
со всеми вытекающими
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
Сообщений: 29867
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

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

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