Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 При построении плана оптимизатор не смотрит данные и статистику в переменой типа table  [new]
Оптимизатор и переменная типа ta
Guest
В хранимке есть сложный запрос с участием временной таблицы #Tmp.
План запроса зависит от данных в #Tmp.
Чтобы решить эту проблему добавлен хинт option (recompile).
С ним запрос работает хорошо с разным набором данных в #Tmp.

Если временную таблицу #Tmp в запросе заменить на переменную типа table @Tmp, то по результатам тестов получается, что при построении плана оптимизатор не смотрит данные и статистику в переменой типа table. Т.е. план для запроса строится не оптимальный.

В связи с чем вопрос - можно ли как-то указать оптимизатору, чтобы он "смотрел" данные в переменной типа table? Или с ней оптимизатор будет всегда работать исходя из общих предположений о данных в ней?
19 май 17, 12:39    [20495936]     Ответить | Цитировать Сообщить модератору
 Re: При построении плана оптимизатор не смотрит данные и статистику в переменой типа table  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 3317
специфика работы с переменными таблицами. Оптимизатор принимает количество записей как 1. Есть флаг, с 2012+. или OPTION (RECOMPILE). а вообще не ясно зачем вам в хранимке @ вместо #
19 май 17, 12:47    [20495995]     Ответить | Цитировать Сообщить модератору
 Re: При построении плана оптимизатор не смотрит данные и статистику в переменой типа table  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 3317
хотя "специфика", это я зря... табличные переменные это не допиленный костыль и всё с ним плохо :) и юзать наверное только в функция из-за необходимости
19 май 17, 12:51    [20496021]     Ответить | Цитировать Сообщить модератору
 Re: При построении плана оптимизатор не смотрит данные и статистику в переменой типа table  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 5043
Оптимизатор и переменная типа ta,

так не кладите много строк в табличную переменную и жизнь наладится.
19 май 17, 13:11    [20496162]     Ответить | Цитировать Сообщить модератору
 Re: При построении плана оптимизатор не смотрит данные и статистику в переменой типа table  [new]
o-o
Guest
статистик именно статистик у table variables просто нет.
есть cardinality, но ТС написал, и так использует option (recompile):
Оптимизатор и переменная типа ta
Чтобы решить эту проблему добавлен хинт option (recompile).

а распределения значений нету, да, т.е. гистограммы нет и не будет
19 май 17, 13:13    [20496182]     Ответить | Цитировать Сообщить модератору
 Re: При построении плана оптимизатор не смотрит данные и статистику в переменой типа table  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 3317
Владислав Колосов
Оптимизатор и переменная типа ta,

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

так себе решение :) весь вопрос что дальше с ней делается, пару недель назад пожаловались что не понятно почему очень долго отрабатывает процедура, в итоге нашлась табличка с 10 записями которая соединялась с миллионниками и усё :)
19 май 17, 13:14    [20496185]     Ответить | Цитировать Сообщить модератору
 Re: При построении плана оптимизатор не смотрит данные и статистику в переменой типа table  [new]
TaPaK
Member

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

автор
статистик именно статистик у table variables просто нет.

гистограммы, статистики... для одной записи :)
19 май 17, 13:16    [20496211]     Ответить | Цитировать Сообщить модератору
 Re: При построении плана оптимизатор не смотрит данные и статистику в переменой типа table  [new]
Mr. X
Guest
Оптимизатор и переменная типа ta,

У табличных переменных нет статистики и априори считается что в них 1 строка. И те и др располагаются в tempdb. Смысла менять # на @ не вижу.
19 май 17, 13:17    [20496225]     Ответить | Цитировать Сообщить модератору
 Re: При построении плана оптимизатор не смотрит данные и статистику в переменой типа table  [new]
LSV
Member

Откуда: Киев
Сообщений: 29899
Смысла менять # на @ не вижу.
+500. # намного удобнее.
19 май 17, 13:36    [20496338]     Ответить | Цитировать Сообщить модератору
 Re: При построении плана оптимизатор не смотрит данные и статистику в переменой типа table  [new]
человек_ниоткуда
Guest
Оптимизатор и переменная типа ta
В связи с чем вопрос - можно ли как-то указать оптимизатору, чтобы он "смотрел" данные в переменной типа table? Или с ней оптимизатор будет всегда работать исходя из общих предположений о данных в ней?

Указать можно заменив @table на #table. :)
...Исходя из предположения, сколько примерно записей было в @table на момент компиляции запроса (обычно 0).

TaPaK
специфика работы с переменными таблицами. Оптимизатор принимает количество записей как 1.

Не совсем так...

----------------

Для табличных переменных статистики ИЛИ не создаются, ИЛИ игнорируются. Оптимизатор смотрит только на значение в sys.partitions.rows. Профит от @таблиц в том что их использование исключает рекомпиляцию вызванную DML запросами (например "CREATE TABLE #table"); что очень помогает для часто-запускаемых, но лёгких запросах.
Ещё, конечно, @table живут столько же сколько переменные, т.е. до окончания batch-а.
В остальном #table и @table физически одно и то же.
19 май 17, 14:18    [20496533]     Ответить | Цитировать Сообщить модератору
 Re: При построении плана оптимизатор не смотрит данные и статистику в переменой типа table  [new]
msLex
Member

Откуда:
Сообщений: 4912
человек_ниоткуда
В остальном #table и @table физически одно и то же.

А как же нетранзакционность, главная фича @table ?
19 май 17, 14:27    [20496568]     Ответить | Цитировать Сообщить модератору
 Re: При построении плана оптимизатор не смотрит данные и статистику в переменой типа table  [new]
o-o
Guest
человек_ниоткуда
Не совсем так...
Для табличных переменных статистики ИЛИ не создаются, ИЛИ игнорируются.

пример-то можно, когда они "создаются, но игнорируются"?
а то мое "не совсем так" выглядит след. образом:
o-o
статистик именно статистик у table variables просто нет.
есть cardinality, но ТС написал, и так использует option (recompile):
Оптимизатор и переменная типа ta
Чтобы решить эту проблему добавлен хинт option (recompile).

а распределения значений нету, да, т.е. гистограммы нет и не будет
19 май 17, 14:28    [20496577]     Ответить | Цитировать Сообщить модератору
 Re: При построении плана оптимизатор не смотрит данные и статистику в переменой типа table  [new]
человек_ниоткуда
Guest
o-o
пример-то можно, когда они "создаются, но игнорируются"?

DECLARE @t TABLE (id INT NOT NULL PRIMARY KEY)
19 май 17, 14:55    [20496697]     Ответить | Цитировать Сообщить модератору
 Re: При построении плана оптимизатор не смотрит данные и статистику в переменой типа table  [new]
invm
Member

Откуда: Москва
Сообщений: 7398
msLex
А как же нетранзакционность, главная фича @table ?
Термин сбивает с толку, т.к. порождает мысли о нелогируемости манипуляций с @table.
ИМХО, гораздо лучше подходит "автономная транзакционность".
19 май 17, 14:59    [20496715]     Ответить | Цитировать Сообщить модератору
 Re: При построении плана оптимизатор не смотрит данные и статистику в переменой типа table  [new]
o-o
Guest
человек_ниоткуда
o-o
пример-то можно, когда они "создаются, но игнорируются"?

DECLARE @t TABLE (id INT NOT NULL PRIMARY KEY)

и что, где статистика-то?
19 май 17, 15:23    [20496798]     Ответить | Цитировать Сообщить модератору
 Re: При построении плана оптимизатор не смотрит данные и статистику в переменой типа table  [new]
invm
Member

Откуда: Москва
Сообщений: 7398
o-o
где статистика-то?
declare @t table (id int primary key, [@t dummy column] int);

select
 s.*
from
 sys.columns c join
 sys.stats s on s.object_id = c.object_id
where
 c.name = '@t dummy column';
19 май 17, 15:39    [20496851]     Ответить | Цитировать Сообщить модератору
 Re: При построении плана оптимизатор не смотрит данные и статистику в переменой типа table  [new]
o-o
Guest
invm
o-o
где статистика-то?
declare @t table (id int primary key, [@t dummy column] int);

select
 s.*
from
 sys.columns c join
 sys.stats s on s.object_id = c.object_id
where
 c.name = '@t dummy column';

ну так пусто же.
или у вас вдруг нет?
19 май 17, 15:48    [20496883]     Ответить | Цитировать Сообщить модератору
 Re: При построении плана оптимизатор не смотрит данные и статистику в переменой типа table  [new]
o-o
Guest
убедиться, что статистики нет, можно и просто глядя на оценки:

выполняем один и тот же код по заполнению темповой таблицы и переменной
числами 1..1000000.
удаляем почти все (id > 500)
запрашиваем удаленные строки (BETWEEN 1000 AND 1000000)
в случае с таблицей статистика есть, результат оценивается в 1 строку
(0 он просто никогда не прогнозирует, 1 это и есть показатель пустого результата), ибо таких строк просто НЕТ.
потому что статистика распределения доступна, она даже обновилась после такого массового удаления.

в случае с переменной предполагаются 45 строк.
ну да, он только кардиналити видит, знает, что 500 строк всего,
и что это за строки он понятия не имеет.
предикат у нас BETWEEN,
в отсутствие статистики оценка 9%.
вот и получили 45 строк.

К сообщению приложен файл. Размер - 91Kb
19 май 17, 16:04    [20496941]     Ответить | Цитировать Сообщить модератору
 Re: При построении плана оптимизатор не смотрит данные и статистику в переменой типа table  [new]
человек_ниоткуда
Guest
o-o
...
ну так пусто же.
или у вас вдруг нет?

У меня работает: на SQL 10.5 и SQL 12
DECLARE @t82362346249249 TABLE(id6168168168163818681 INT PRIMARY KEY)

SELECT	*
FROM
	tempdb.sys.tables t
INNER JOIN
	tempdb.sys.indexes AS i
ON	i.[object_id] = t.[object_id]
WHERE
	EXISTS
	(SELECT	*
	 FROM	tempdb.sys.[columns] c
	 WHERE 
		c.[object_id] = t.[object_id]
	AND	c.name LIKE '%id6168168168163818681%');
	
SELECT	*
FROM
	tempdb.sys.tables t
INNER JOIN
	tempdb.sys.[stats] AS i
ON	i.[object_id] = t.[object_id]
WHERE
	EXISTS
	(SELECT	*
	 FROM	tempdb.sys.[columns] c
	 WHERE 
		c.[object_id] = t.[object_id]
	AND	c.name LIKE '%id6168168168163818681%');


Вообщето в MSDN написано, что кажды индекс имеет статистку, и это зашито в архитектуру.
19 май 17, 16:37    [20497054]     Ответить | Цитировать Сообщить модератору
 Re: При построении плана оптимизатор не смотрит данные и статистику в переменой типа table  [new]
aleks2
Guest
Оптимизатор и переменная типа ta
В хранимке есть сложный запрос с участием временной таблицы #Tmp.
План запроса зависит от данных в #Tmp.
Чтобы решить эту проблему добавлен хинт option (recompile).
С ним запрос работает хорошо с разным набором данных в #Tmp.

Если временную таблицу #Tmp в запросе заменить на переменную типа table @Tmp, то по результатам тестов получается, что при построении плана оптимизатор не смотрит данные и статистику в переменой типа table. Т.е. план для запроса строится не оптимальный.

В связи с чем вопрос - можно ли как-то указать оптимизатору, чтобы он "смотрел" данные в переменной типа table? Или с ней оптимизатор будет всегда работать исходя из общих предположений о данных в ней?


Да, сочувствую.
Здешние гуру выли тонны словесного поноса.

Секрет @table прост - их создают под конкретные вычисления.
Дык, не надо лениться - надо создать их так, чтобы был правильный уникальный индекс.
19 май 17, 16:47    [20497099]     Ответить | Цитировать Сообщить модератору
 Re: При построении плана оптимизатор не смотрит данные и статистику в переменой типа table  [new]
o-o
Guest
код invm у меня выдает пусто, хоть тресни.
код человек_ниоткуда показывает статистику с именем PK__#A5B6FBA__A2E853DA398BF303.
только вот то, что я вижу в оценках, это как раз случай No statistics are available.
тогда предлагаю сойтись на этом самом, not available.
потому что если даже и есть, то все равно not available.
т.е. мне даже все равно, как это назвать, что она недоступна, игнорируется или еще что,
по факту она не используется, т.е. эффект тот же, что ее и нет.
но если приятнее знать, что она есть, то пускай она есть :)
с нулевой полезностью

К сообщению приложен файл. Размер - 120Kb
19 май 17, 16:57    [20497115]     Ответить | Цитировать Сообщить модератору
 Re: При построении плана оптимизатор не смотрит данные и статистику в переменой типа table  [new]
o-o
Guest
aleks2

Дык, не надо лениться - надо создать их так, чтобы был правильный уникальный индекс.

тя не научил пример выше с ПК?
ПК весьма уникален, но на оценки не повлиял.
создай индекс правильнее, чтобы его существующую статистику
заюзали при оценке селекта после делита
19 май 17, 16:59    [20497121]     Ответить | Цитировать Сообщить модератору
 Re: При построении плана оптимизатор не смотрит данные и статистику в переменой типа table  [new]
aleks2
Guest
o-o
aleks2

Дык, не надо лениться - надо создать их так, чтобы был правильный уникальный индекс.

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


Ты ж ы курсе - я игнорирую высосанные из пальца примеры.

Скока ни имел я дела с @table - проблем с замедлениями запросов я не имел.
Запрос надо писать так, чтобы у оптимизатора не оставалось выбора.
19 май 17, 18:35    [20497410]     Ответить | Цитировать Сообщить модератору
 Re: При построении плана оптимизатор не смотрит данные и статистику в переменой типа table  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 1884
aleks2
Запрос надо писать так, чтобы у оптимизатора не оставалось выбора.
Plan Guide на каждый запрос. А че мелочиться то?
19 май 17, 22:05    [20497757]     Ответить | Цитировать Сообщить модератору
 Re: При построении плана оптимизатор не смотрит данные и статистику в переменой типа table  [new]
uaggster
Member

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

Дык, не надо лениться - надо создать их так, чтобы был правильный уникальный индекс.

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

Option (recompile), же, вроде, в таком случае (в 2014) правит оценку кардинальности, и эстимейтед будет уже не 1?

А в 2016, вроде как, и индексы можно в табличных переменных создавать?
22 май 17, 11:01    [20500655]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить