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

Откуда:
Сообщений: 98
Добрый день!

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

DECLARE @CurrentDate DATE = '2017-02-15'

-- Определение границ прошлого месяца
DECLARE @LeftDateTimeMargin  DATETIME = DATEADD(MONTH, DATEDIFF( MONTH, 0, @CurrentDate )-1, 0)
DECLARE @RightDateTimeMargin DATETIME = DATEADD(MONTH, DATEDIFF( MONTH, 0, @CurrentDate ),   0)

-- debug 
-- SELECT @CurrentDate as CurrentDate, @LeftDateTimeMargin as LeftDateTimeMargin, @RightDateTimeMargin as RightDateTimeMargin

-- Создание табличной переменной для сохранения выборки данных за целевой период
DECLARE @SourceData TABLE (
	[id] [uniqueidentifier] NOT NULL PRIMARY KEY,
	[event_datetime] [datetime] NOT NULL,
	[base_id] [uniqueidentifier] NOT NULL,
	[event_id] [uniqueidentifier] NOT NULL,
	[user_id] [uniqueidentifier] NULL,
	[metaname] [nvarchar](300) NOT NULL,
	[duration] [int] NULL,
	[product_id] [uniqueidentifier] NOT NULL,
	[server_event_datetime] [datetime] NOT NULL,
	[aeh_id] [uniqueidentifier] NULL
)

-- Заполнение табличной перемнной данными с отбором (число строк = 6 599 436)
INSERT INTO @SourceData (
	[id],
	[event_datetime],
	[base_id],
	[event_id],
	[user_id],
	[metaname],
	[duration],
	[product_id],
	[server_event_datetime],
	[aeh_id]
) SELECT 
	[id],
	[event_datetime],
	[base_id],
	[event_id],
	[user_id],
	[metaname],
	[duration],
	[product_id],
	[server_event_datetime],
	[aeh_id]
FROM [StatSoftwareUsageData]
WHERE
	server_event_datetime >= @LeftDateTimeMargin AND
	server_event_datetime <  @RightDateTimeMargin

-- ОЧЕНЬ ДОЛГО РАБОТАЕТ, БОЛЕЕ 20+ МИНУТ - в качестве источника, заранее подготовленная выборка
-- При исполнении запроса, sqlservr грузит 1 ядро на 100%

SELECT
	DISTINCT [@SourceData].[metaname]
FROM @SourceData
LEFT JOIN [StatMetanames] ON [StatMetanames].[metaname] = [@SourceData].[metaname]
WHERE
	[StatMetanames].[id] IS NULL

-- РАБОТАЕТ НОРМАЛЬНО (20 сек) - в качестве источника, обычная таблица
-- При исполнении запроса, sqlservr грузит все ядра

--SELECT
--	DISTINCT [StatSoftwareUsageData].[metaname]
--FROM [StatSoftwareUsageData]
--LEFT JOIN [StatMetanames] ON [StatMetanames].[metaname] = [StatSoftwareUsageData].[metaname]
--WHERE
--	[StatMetanames].[id] IS NULL AND
--	server_event_datetime >= @LeftDateTimeMargin AND
--	server_event_datetime <  @RightDateTimeMargin

Понятно, что по строке соединяться - не самая лучшая затея. Но вопрос не в этом.
Вопрос - почему такая существенная разница, при работе с одинаковым числом записей?
Табличная переменная на порядки медленнее чем запрос к исходной таблице.
Есть ли возможность как-то использовать табличные переменные, не теряя производительность?
Почему грузится 1 ядро при работе с табличной переменной?

Казалось бы, табличная переменная целиком в памяти, вместо того, чтобы 10 раз прицеплять вложенный запрос к исходной таблице,
должно быть лучше создать 1 раз нужную выборку и везде её использовать...
3 мар 17, 15:24    [20260741]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменная VS вложенный запрос  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
SolidSnake,

sql оценивает количество записей в табличной переменной всегда как 1 (если не ошибаюсь) вот из этого и строит план. В последней версии вроде есть оптимизация под них...
3 мар 17, 15:26    [20260749]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменная VS вложенный запрос  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
https://support.microsoft.com/en-us/help/2952444/fix-poor-performance-when-you-use-table-variables-in-sql-server-2012-or-sql-server-2014
3 мар 17, 15:28    [20260760]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменная VS вложенный запрос  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
SolidSnake
Казалось бы, табличная переменная целиком в памяти
Ага, еще долго не переведутся люди, которым так кажется.

SolidSnake
сто того, чтобы 10 раз прицеплять вложенный запрос к исходной таблице,
должно быть лучше создать 1 раз нужную выборку и везде её использовать...
Покажите уже планы выполнения.
3 мар 17, 15:30    [20260763]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменная VS вложенный запрос  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Гавриленко Сергей Алексеевич
SolidSnake
Казалось бы, табличная переменная целиком в памяти
Ага, еще долго не переведутся люди, которым так кажется.

о даааа, упустил.. Никогда не было, и вот опять :)
3 мар 17, 15:31    [20260769]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменная VS вложенный запрос  [new]
SolidSnake
Member

Откуда:
Сообщений: 98
План вложен

К сообщению приложен файл. Размер - 71Kb
3 мар 17, 15:42    [20260809]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменная VS вложенный запрос  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Еще бы оно работало быстро -- у вас на каждую запись во времягке полностью сканируется таблица StatMetanames
3 мар 17, 15:47    [20260833]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменная VS вложенный запрос  [new]
Нектотам
Guest
SolidSnake,

1. Присоединяюсь к вопросу о планах.
2. Какая структура у StatSoftwareUsageData и какие индексы? А то может по left join и StatMetanames.id IS NULL фильтруется до 3 записей.
3. В случае с табличной переменной вы читаете из StatSoftwareUsageData и пишите в @SourceData 10 колонок, а в простом запросе - 2.
4. Табличная переменная живет в tempdb. Это вполне может привести к большим IO.
5. В случае с табличной переменной сначала надо выгрести все записи, а потом уже делать join.
6. В случае с табличной переменной оптимизатор часто промахивается, особенно без option(recompile), т.к. статистики по табличной переменной нет.

И это только начало различий, просто продолжать после п. 1 смысла нет.
3 мар 17, 15:50    [20260848]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменная VS вложенный запрос  [new]
SolidSnake
Member

Откуда:
Сообщений: 98
Т.е. в этом плане, всё радикально лучше?
Вроде тоже самое, но выполняется за 19 сек, против 20 минут. Разница какая-то феноменальная просто.

Разгадка должна быть в том, что при работе с табличной переменной, используется 1 ядро процессора.
Тогда как, при запросе к реальной таблице, все ядра.

К сообщению приложен файл. Размер - 87Kb
3 мар 17, 15:51    [20260858]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменная VS вложенный запрос  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
SolidSnake
Разгадка должна быть в том, что при работе с табличной переменной, используется 1 ядро процессора.
Конечно же в этом, в инопланетянах или магнитных бурях, но никак не в том, что для соединения используется другой физический оператор.
3 мар 17, 15:53    [20260869]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменная VS вложенный запрос  [new]
aleks2
Guest
Тредстартер чудак на букву м.

Ставлю на 5 сек.

DECLARE @CurrentDate DATE = '2017-02-15'

-- Определение границ прошлого месяца
DECLARE @LeftDateTimeMargin  DATETIME = DATEADD(MONTH, DATEDIFF( MONTH, 0, @CurrentDate )-1, 0)
DECLARE @RightDateTimeMargin DATETIME = DATEADD(MONTH, DATEDIFF( MONTH, 0, @CurrentDate ),   0)

-- debug 
-- SELECT @CurrentDate as CurrentDate, @LeftDateTimeMargin as LeftDateTimeMargin, @RightDateTimeMargin as RightDateTimeMargin

-- Создание табличной переменной для сохранения выборки данных за целевой период
DECLARE @SourceData TABLE (
	[metaname] [nvarchar](300) PRIMARY KEY,
)

-- Заполнение табличной перемнной данными с отбором (число строк = 6 599 436)
INSERT INTO @SourceData (
	[metaname],
) SELECT 
	distinct [metaname]
FROM [StatSoftwareUsageData]
WHERE
	server_event_datetime >= @LeftDateTimeMargin AND
	server_event_datetime <  @RightDateTimeMargin

-- ОЧЕНЬ ДОЛГО РАБОТАЕТ, БОЛЕЕ 20+ МИНУТ - в качестве источника, заранее подготовленная выборка
-- При исполнении запроса, sqlservr грузит 1 ядро на 100%

SELECT
	[@SourceData].[metaname]
FROM @SourceData
WHERE  not exists(select * from [StatMetanames] where [StatMetanames].[metaname] = [@SourceData].[metaname])
3 мар 17, 15:54    [20260873]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменная VS вложенный запрос  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
SolidSnake,

автор
Разгадка должна быть в том, что при работе с табличной переменной, используется 1 ядро процессора.
Тогда как, при запросе к реальной таблице, все ядра.

нет. ещё раз перечитайте первый ответ.
во втором случае нормальная оценка даёт распараллелить запрос и hash join, что даёт большую нагрузку на cpu
3 мар 17, 15:54    [20260878]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменная VS вложенный запрос  [new]
aleks2
Guest
TaPaK
SolidSnake,

автор
Разгадка должна быть в том, что при работе с табличной переменной, используется 1 ядро процессора.
Тогда как, при запросе к реальной таблице, все ядра.

нет. ещё раз перечитайте первый ответ.
во втором случае нормальная оценка даёт распараллелить запрос и hash join, что даёт большую нагрузку на cpu


Опять ты неправильно излагаешь.

Во втором случае глупость тредстартера оптимизатору удается компенсировать параллелизмом.
В первом - глупость погребла под собой оптимизатор.
3 мар 17, 15:58    [20260898]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменная VS вложенный запрос  [new]
Нектотам
Guest
aleks2,

OPTION(RECOMPILE) еще в конце добавить, чтобы актуальное количество строк использовал :)
3 мар 17, 16:00    [20260904]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменная VS вложенный запрос  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 942
TaPaK
https://support.microsoft.com/en-us/help/2952444/fix-poor-performance-when-you-use-table-variables-in-sql-server-2012-or-sql-server-2014

The trace flag 2453 allows the benefit of query recompile without OPTION (RECOMPILE). This trace flag differs from OPTION (RECOMPILE) in two main aspects.
(1) It uses the same row count threshold as other tables. The query does not need to be compiled for every execution unlike OPTION (RECOMPILE). It would trigger recompile only when the row count change exceeds the predefined threshold.
(2) OPTION (RECOMPILE) forces the query to peek parameters and optimize the query for them. This trace flag does not force parameter peeking.

На основании написанного и своих познаний, я так понимаю, что п2 имитируется = OPTION ( OPTIMIZE FOR UNKNOWN ), и если перейти с @t на #t - опция RECOMPILE тоже становится не нужна?
3 мар 17, 16:01    [20260912]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменная VS вложенный запрос  [new]
aleks2
Guest
Нектотам
aleks2,

OPTION(RECOMPILE) еще в конце добавить, чтобы актуальное количество строк использовал :)


Правильному запросу - хинты не нужны.
3 мар 17, 16:01    [20260915]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменная VS вложенный запрос  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Руслан Дамирович
TaPaK
https://support.microsoft.com/en-us/help/2952444/fix-poor-performance-when-you-use-table-variables-in-sql-server-2012-or-sql-server-2014

The trace flag 2453 allows the benefit of query recompile without OPTION (RECOMPILE). This trace flag differs from OPTION (RECOMPILE) in two main aspects.
(1) It uses the same row count threshold as other tables. The query does not need to be compiled for every execution unlike OPTION (RECOMPILE). It would trigger recompile only when the row count change exceeds the predefined threshold.
(2) OPTION (RECOMPILE) forces the query to peek parameters and optimize the query for them. This trace flag does not force parameter peeking.

На основании написанного и своих познаний, я так понимаю, что п2 имитируется = OPTION ( OPTIMIZE FOR UNKNOWN ), и если перейти с @t на #t - опция RECOMPILE тоже становится не нужна?

нет, смотрите...
DECLARE @a TABLE  (i INT)
INSERT INTO @a SELECT ROW_NUMBER() OVER (ORDER BY 1/0 ) FROM master..spt_values
SELECT i FROM @a OPTION(OPTIMIZE  FOR UNKNOWN)
SELECT i FROM @a OPTION(RECOMPILE)

занятно ms пишет решается так, но мы так делать конечно же не будем :)


автор
Во втором случае глупость тредстартера оптимизатору удается компенсировать параллелизмом.
В первом - глупость погребла под собой оптимизатор.

речь идёт по чему так, а не как правильно
3 мар 17, 16:07    [20260933]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменная VS вложенный запрос  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
DBCC TRACEON(2453) занятно работает, оценка не меняется, а план становится правильнее
3 мар 17, 16:19    [20260984]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменная VS вложенный запрос  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 942
TaPaK
занятно ms пишет решается так, но мы так делать конечно же не будем :)

Занятно, что такой знающий человек слепо следует рекомендациям, не понимая, что они делают? Сомневаюсь.

Я не понимаю этих TRACEFLAG - для меня это какие-то костыли, потому что их нужно включать/выключать.
В то время как опции в конце - это часть запроса, и воспринимается мной положительно. Это имхо.

А теперь приведу свои соображения:
1. RECOMPILE просто не сохраняет план запроса в кэш, и потому план при следующем запуске перестраивается.
2. Поэтому MS рекомендует использовать TF, который:
а. Позволяет рекомпиляцию запроса только если в таблице изменились записи по правилам для обычных таблиц.
Я так понимаю, что для временных таблиц аналогичное правило работает по умолчанию
б. Отключает parameters sniffing. Что можно сделать через OPTIMIZE FOR UNKNOWN
Поэтому и спрашиваю, зачем связываться с @t и TF, а не перейти на # с OPTION, у которых еще и куча других преимуществ по отношению к @t?
3 мар 17, 16:20    [20260991]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменная VS вложенный запрос  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Руслан Дамирович,

ёмаё... да где вы увидели, что кто-то рекомендует табличные переменные?? сущее зло и само по себе костыль.
автор
OPTION, у которых еще и куча других преимуществ по отношению к @t?

Я придерживаюсь той же точки зрения "Правильному запросу - хинты не нужны."

автор
1. RECOMPILE просто не сохраняет план запроса в кэш, и потому план при следующем запуске перестраивается.

ой да ладно прям только не сохраняет, учите матчасть

автор
2. Поэтому MS рекомендует использовать TF, который:
sp и cu вы тоже наверное не ставите...
3 мар 17, 16:24    [20261014]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменная VS вложенный запрос  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Руслан Дамирович,

автор
а. Позволяет рекомпиляцию запроса только если в таблице изменились записи по правилам для обычных таблиц.

это откуда такой вывод? Прекратите фантазировать
автор
Function: When trace flag 2453 is active, the optimizer can obtain an accurate picture of table cardinality after the table variable has been created.
3 мар 17, 16:29    [20261035]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменная VS вложенный запрос  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 942
TaPaK
Я придерживаюсь той же точки зрения "Правильному запросу - хинты не нужны."

...и в метро на эскалаторах только по правой стороне перемещаетесь.

TaPaK
ой да ладно прям только не сохраняет, учите матчасть

И что же нового я там для себя открою?
Нет, серьезно, можно мне TLTR из описания RECOMPILE?

sp и cu вы тоже наверное не ставите...

А зачем? У меня никогда не было USE CASE, чтобы вот прям нужно было "накатить патчей, а то запрос не работает". Если за что и ставили - чтобы фичи "потрогать".
3 мар 17, 16:36    [20261069]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменная VS вложенный запрос  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 942
TaPaK
Function: When trace flag 2453 is active, the optimizer can obtain an accurate picture of table cardinality after the table variable has been created.

Источник цитаты можно, а то по вашей ссылке (единственной)
+ More Information

When you use a table variable in a batch or procedure, the query is compiled and optimized for the initial empty state of table variable. If this table variable is populated with many rows at runtime, the pre-compiled query plan may no longer be optimal. For example, the query may be joining a table variable with nested loop since it is usually more efficient for small number of rows. This query plan can be inefficient if the table variable has millions of rows. A hash join may be a better choice under such condition. To get a new query plan, it needs to be recompiled. Unlike other user or temporary tables, however, row count change in a table variable does not trigger a query recompile. Typically, you can work around this with OPTION (RECOMPILE), which has its own overhead cost.
The trace flag 2453 allows the benefit of query recompile without OPTION (RECOMPILE). This trace flag differs from OPTION (RECOMPILE) in two main aspects.
(1) It uses the same row count threshold as other tables. The query does not need to be compiled for every execution unlike OPTION (RECOMPILE). It would trigger recompile only when the row count change exceeds the predefined threshold.
(2) OPTION (RECOMPILE) forces the query to peek parameters and optimize the query for them. This trace flag does not force parameter peeking.

Note this trace flag must be ON at runtime. You cannot use this trace flag with QUERYTRACEON. This trace flag must be used with caution because it can increase number of query recompiles which could cost more than savings from better query optimization.
3 мар 17, 16:40    [20261083]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменная VS вложенный запрос  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Руслан Дамирович,

выпиливаюсь из дискуссии, с комментарием "данунафиг"
3 мар 17, 16:42    [20261087]     Ответить | Цитировать Сообщить модератору
 Re: Табличная переменная VS вложенный запрос  [new]
SolidSnake
Member

Откуда:
Сообщений: 98
Нектотам,

Нектотам
2. Какая структура у StatSoftwareUsageData и какие индексы?
Поля 1 в 1 как у @SourceData, индекс кластеризованный на [id] и не кластеризованный на [server_event_datetime].

Нектотам
3. В случае с табличной переменной вы читаете из StatSoftwareUsageData и пишите в @SourceData 10 колонок, а в простом запросе - 2.
Выполняется 20 минут инструкция после комментария (@SourceData заполняется достаточно быстро).
При этом, если в быстром запросе добавить все колонки из медленного, скорость исполнения не меняется.

Нектотам
4. Табличная переменная живет в tempdb. Это вполне может привести к большим IO.
Разве не временные таблицы живут в TempDB? Тогда как табличные переменные в памяти?
В IO сервер не упирается при исполнении медленного запроса.

автор
5. В случае с табличной переменной сначала надо выгрести все записи, а потом уже делать join.
Причина, почему в табличной переменной много колонок, в то время, как тестовый запрос использует только одну -
это тестовый запрос, тут минимум кода, но результрующая выборка со всеми колонками может использоваться.
Т.е. идея заключалась в том, чтобы 1 раз сформировать выборку (а ведь она может быть сложной и долгой), положить результат куда-то и при необходимости использовать, вместо того, чтобы заново формировать выборку.

автор
6. В случае с табличной переменной оптимизатор часто промахивается, особенно без option(recompile), т.к. статистики по табличной переменной нет.
Спасибо!
Добавление "OPTION (RECOMPILE)" к медленному запросу полностью устраняет разницу, одновременно с этим исполнение параллелится.
Это правильный ответ на вопрос.

Теперь хоть понятно откуда и почему возникает разница в тестовом запросе.

TaPaK
Спасибо.

aleks2
Никогда не понимал таких людей.
Ты поди эксперт во всём, раз допускаешь такие комментарии.
Опимизация тестового примера не дает ответ на вопрос.
3 мар 17, 16:47    [20261106]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить