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

Откуда:
Сообщений: 107
Есть пользовательская функция, которая при обработке базы вызывается для каждой записи (записей пока ок. 1000, но ожидается увеличение числа на два порядка). В этой функции создается табличная переменная, чтоб сформировать определенный набор записей (при этом используются операции вставки, обновления, удаления), который потом уже скидывается в постоянную таблицу.

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

А что думают по этому поводу гуру?
19 апр 05, 17:14    [1480397]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменна vs Постоянная таблица - есть ли выигрышь?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37101
Единственно отличие переменной от таблицы то, что операции с переменной не пишутся в транзакт-лог. Т.е. по идее д.б. быстрее. Но из-за некоторых ограничений, типа невозможности вешать индексы, м.б., в итоге, медленнее.
19 апр 05, 17:19    [1480432]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменна vs Постоянная таблица - есть ли выигрышь?  [new]
Glory
Member

Откуда:
Сообщений: 104760
А что думают по этому поводу гуру?
Я лично полагаю что вам надо думать на д тем как избавится от "функция, которая при обработке базы вызывается для каждой записи ".
Потому что при 100000 записей уже будет все равно быстрее ли табличная переменная временно таблицы или медленее.

Я не знаю, при объявлении табличной переменной создается ли соотвествующая ей таблица в физической базе или она вся в памяти?
А вы глянте на содержимое tempdb..sysobjects после объявления табличной переменной
19 апр 05, 17:30    [1480488]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменна vs Постоянная таблица - есть ли выигрышь?  [new]
alextus
Member

Откуда:
Сообщений: 94
автор
Я не знаю, при объявлении табличной переменной создается ли соотвествующая ей таблица в физической базе или она вся в памяти?

select * from tempdb..sysobjects
#540DCC42
надо же, а я думал временная таблица создается в оперативной памяти
19 апр 05, 17:49    [1480590]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменна vs Постоянная таблица - есть ли выигрышь?  [new]
Buba.
Member

Откуда:
Сообщений: 107
Glory
Я лично полагаю что вам надо думать над тем как избавится от "функция, которая при обработке базы вызывается для каждой записи ".

Рад бы, но увы! Весьма проблематично. Это все в рамках инет-проекта работает, есть условия, которые не позволяют обрабатывать иначе.

Glory
А вы глянте на содержимое tempdb..sysobjects после объявления табличной переменной

Спасибо, понял
19 апр 05, 18:23    [1480712]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменна vs Постоянная таблица - есть ли выигрышь?  [new]
Yo!!
Guest
гуру считают, что быстрее будет только на простеньких задачках в однопользовательском режиме.

http://www.sql-server-performance.com/temp_tables.asp
19 апр 05, 20:17    [1480812]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменна vs Постоянная таблица - есть ли выигрышь?  [new]
andsm
Member

Откуда: Москва
Сообщений: 1319
Блог
Гавриленко Сергей Алексеевич
Единственно отличие переменной от таблицы то, что операции с переменной не пишутся в транзакт-лог. Т.е. по идее д.б. быстрее. Но из-за некоторых ограничений, типа невозможности вешать индексы, м.б., в итоге, медленнее.

Операции со временной таблицей в лог транзакций пишутся.
19 апр 05, 20:58    [1480859]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменна vs Постоянная таблица - есть ли выигрышь?  [new]
Buba.
Member

Откуда:
Сообщений: 107
Yo!!
гуру считают, что быстрее будет только на простеньких задачках в однопользовательском режиме.

http://www.sql-server-performance.com/temp_tables.asp

Поправьте, если ошибаюсь, но там речь идет о временных таблицах, а не о табличных переменных. А в одной умной книжке прочитал, что временные таблицы сильно жрут ресурсы, поэтому там где раньше использовались временные таблицы сейчас (т.е. в SQL2000) по возможности лучше использовать табличные переменные. Или такие книжки совсем не умные?

А попутно задумался относительно табличных функций - там ведь тоже физически временные таблицы создаются, стало быть не стоит ими злоупотреблять.
20 апр 05, 03:20    [1481103]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменна vs Постоянная таблица - есть ли выигрышь?  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Buba.
Yo!!
гуру считают, что быстрее будет только на простеньких задачках в однопользовательском режиме.

http://www.sql-server-performance.com/temp_tables.asp

Поправьте, если ошибаюсь, но там речь идет о временных таблицах, а не о табличных переменных. А в одной умной книжке прочитал, что временные таблицы сильно жрут ресурсы, поэтому там где раньше использовались временные таблицы сейчас (т.е. в SQL2000) по возможности лучше использовать табличные переменные. Или такие книжки совсем не умные?
От чего же? Это даже BOL советует:

Use table variables instead of temporary tables, whenever possible. - http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ta-tz_7ysl.asp
20 апр 05, 06:35    [1481154]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменна vs Постоянная таблица - есть ли выигрышь?  [new]
Yo!!
Guest
Buba.

Поправьте, если ошибаюсь, но там речь идет о временных таблицах, а не о табличных переменных. А в одной умной книжке прочитал, что временные таблицы сильно жрут ресурсы, поэтому там где раньше использовались временные таблицы сейчас (т.е. в SQL2000) по возможности лучше использовать табличные переменные. Или такие книжки совсем не умные?


да, про временные это я не в тему ... на счет умной книжки тут (https://www.sql.ru/forum/actualthread.aspx?tid=15751 ) говорят что такое в BOLе написано.
20 апр 05, 10:29    [1481532]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменна vs Постоянная таблица - есть ли выигрышь?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37101
andsm
Гавриленко Сергей Алексеевич
Единственно отличие переменной от таблицы то, что операции с переменной не пишутся в транзакт-лог. Т.е. по идее д.б. быстрее. Но из-за некоторых ограничений, типа невозможности вешать индексы, м.б., в итоге, медленнее.

Операции со временной таблицей в лог транзакций пишутся.

А я что-то другое написал?
20 апр 05, 10:56    [1481655]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменна vs Постоянная таблица - есть ли выигрышь?  [new]
andsm
Member

Откуда: Москва
Сообщений: 1319
Блог
Гавриленко Сергей Алексеевич
andsm
Гавриленко Сергей Алексеевич
Единственно отличие переменной от таблицы то, что операции с переменной не пишутся в транзакт-лог. Т.е. по идее д.б. быстрее. Но из-за некоторых ограничений, типа невозможности вешать индексы, м.б., в итоге, медленнее.

Операции со временной таблицей в лог транзакций пишутся.

А я что-то другое написал?

Да, действительно - это я невнимательно прочитал написанное.
20 апр 05, 11:46    [1481872]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменна vs Постоянная таблица - есть ли выигрышь?  [new]
LR
Member

Откуда: 8P8C
Сообщений: 2423
...В этой функции создается табличная переменная, чтоб сформировать определенный набор записей (при этом используются операции вставки, обновления, удаления), который потом уже скидывается в постоянную таблицу...

В таком случае табличная переменная - единственно возможное...
20 апр 05, 12:03    [1481942]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменна vs Постоянная таблица - есть ли выигрышь?  [new]
Buba.
Member

Откуда:
Сообщений: 107
tpg
[quot Buba.]От чего же? Это даже BOL советует:

Use table variables instead of temporary tables, whenever possible. - http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ta-tz_7ysl.asp
Ну, если даже там советуют, то можно верить на слово. Хоть и не понял я, "почему они не пересекаются", но да будет так.

LR
...В этой функции создается табличная переменная, чтоб сформировать определенный набор записей (при этом используются операции вставки, обновления, удаления), который потом уже скидывается в постоянную таблицу...

В таком случае табличная переменная - единственно возможное...
Не факт. Коль все равно этот набор должен оказаться в постоянной таблице, то можно, выделив специальное поле, которое этот набор будет идентифицировать, сразу же в постоянной таблице его и формировать, и "шинковать" на месте. У постоянной табицы я могу задать всяческие индексы для ускорения операций - это плюс. При использовании табличной переменной идет многократное создание и удаление физической таблицы - это ИМХО большой минус.
20 апр 05, 19:15    [1484187]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменна vs Постоянная таблица - есть ли выигрышь?  [new]
Glory
Member

Откуда:
Сообщений: 104760
Не факт. Коль все равно этот набор должен оказаться в постоянной таблице, то можно, выделив специальное поле, которое этот набор будет идентифицировать, сразу же в постоянной таблице его и формировать, и "шинковать" на месте.
Вот только внутри UDF все равно можно менять только табличную переменную
20 апр 05, 19:18    [1484201]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменна vs Постоянная таблица - есть ли выигрышь?  [new]
Змей
Member

Откуда: Н.Новгород -> Москва
Сообщений: 192
Buba.
tpg
[quot Buba.]От чего же? Это даже BOL советует:

Use table variables instead of temporary tables, whenever possible. - http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ta-tz_7ysl.asp
Ну, если даже там советуют, то можно верить на слово. Хоть и не понял я, "почему они не пересекаются", но да будет так.


Я тоже поверил на слово. И был неприятно удивлен. Был у меня запрос на основе темповой таблицы. Таблица большая (сотня, другая тысяч строк). В запросе с темповой таблицей время выполнения - секунды. Далее решил последовать рекоммендации заменить временные таблицы на табличные переменные.Фактически все, что сделал - заменил #tmp на @tmp. Результат - время выполнения вместо нескольких секунд - несколько минут. Время формирования самой темповой таблицы осталось таким же. Доверяй, но проверяй...
21 апр 05, 10:41    [1485119]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменна vs Постоянная таблица - есть ли выигрышь?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37101
автор
Результат - время выполнения вместо нескольких секунд - несколько минут

Стоило сравнить планы двух запросов.

На самом деле, если исходить из того, что разница между ними только в записи в лог, то становится очевидно, что если временная таблица не используется активно операциями модификации данных (один раз заливается, например), то выигрыш на заливке можно и не заметить. А вот проигрыш на выборке, особенно во времянке много записей, получить - запросто. Хотя бы потому, что на таблицу можно повесить индексы, в отличии от переменной, на которую можно только UNIQUE констрэйнт повесить (что тоже ведет к созданию, в конце-концов) индекса, но это немного не то.
21 апр 05, 11:01    [1485236]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменна vs Постоянная таблица - есть ли выигрышь?  [new]
Змей
Member

Откуда: Н.Новгород -> Москва
Сообщений: 192
Гавриленко Сергей Алексеевич
автор
Результат - время выполнения вместо нескольких секунд - несколько минут

Стоило сравнить планы двух запросов.

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


В том то и дело, что временная таблица была не индексирована. В плане запроса - Table Scan. Поэтому увидеть увеличение времени никак не ожидал...
21 апр 05, 11:28    [1485369]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменна vs Постоянная таблица - есть ли выигрышь?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37101
автор
В том то и дело, что временная таблица была не индексирована. В плане запроса - Table Scan. Поэтому увидеть увеличение времени никак не ожидал...

Все-таки сравните планы обоих вариантов.
21 апр 05, 11:30    [1485381]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменна vs Постоянная таблица - есть ли выигрышь?  [new]
Змей
Member

Откуда: Н.Новгород -> Москва
Сообщений: 192
Гавриленко Сергей Алексеевич
автор
В том то и дело, что временная таблица была не индексирована. В плане запроса - Table Scan. Поэтому увидеть увеличение времени никак не ожидал...

Все-таки сравните планы обоих вариантов.


сравнил...

12:04:35.073 - начали создавать временную таблицу
12:04:43.760 - создали временную таблицу
rows = 200000
12:04:43.760 - начало запроса
12:04:46.010 - конец запроса

12:04:46.027 - начали создавать временную таблицу (переменную)
12:04:57.977 - создали временную таблицу(переменную)
rows = 200000
12:04:57.977 - начало запроса
12:15:17.677 - конец запроса

К сообщению приложен файл. Размер - 0Kb
21 апр 05, 13:08    [1485919]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменна vs Постоянная таблица - есть ли выигрышь?  [new]
Змей
Member

Откуда: Н.Новгород -> Москва
Сообщений: 192
А вообще запрос вида
select top 1 ...
from #tmp
where not exists (select * from t2 where t2.Column=#tmp.Column)
21 апр 05, 13:12    [1485945]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменна vs Постоянная таблица - есть ли выигрышь?  [new]
Glory
Member

Откуда:
Сообщений: 104760
сравнил...
Ну дык планы то разные
21 апр 05, 13:20    [1485980]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменна vs Постоянная таблица - есть ли выигрышь?  [new]
Змей
Member

Откуда: Н.Новгород -> Москва
Сообщений: 192
Вот именно. Так какой тогда смысл в замене темповой таблицы на переменную?? Получается, что не всегда переменная лучше таблицы и каждый раз надо проверять, не верить на "на слово" BOL-у...
21 апр 05, 13:29    [1486015]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменна vs Постоянная таблица - есть ли выигрышь?  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Ещё бы DDL на временную таблицу увидеть бы и декларацию на табличную переменную.
21 апр 05, 13:33    [1486034]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменна vs Постоянная таблица - есть ли выигрышь?  [new]
Glory
Member

Откуда:
Сообщений: 104760
Получается, что не всегда переменная лучше таблицы и каждый раз надо проверять, не верить на "на слово" BOL-у...
Сравнивать производительность при разных планах - абсурд.
И BOL вовсе не говорит что не надо, чтобы планы не совпадали
21 апр 05, 13:39    [1486068]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить