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

Откуда:
Сообщений: 573
Вижу в запросе табличную функцию, возвращающую четыре id-шки.

select
*
from
table_function(777) tf,
join first_table ft
join second_table... st
...
...

Замена этой функции на обычную, нормальную таблицу с четырмя значениями уменьшает время выполнения в два-три раза.
CPU - в три раза. Количество чтений в 15 раз.
Естественно, план запроса другой.

Отдельно функция выполняется мгновенно, с почти нулевыми затратами по CPU и чтениям.
Попытка заранее выполнить функцию и заполнить табличную переменную,
declare @table_value table (id int)
...
...
все остается так-же плохо!

В чем причина? Можно ли грамотно реализовать использование функции, табличной переменной вместо обычной таблицы?
Спасибо!
3 фев 14, 09:14    [15508407]     Ответить | Цитировать Сообщить модератору
 Re: обычная таблица vs. табличная функция в запросе  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3274
Сделать функцию инлайн, если это возможно.

Попробовать объявить табличную переменную как declare @table_value table (id int primary key). Ну или временную таблицу сделать, с полноценным ключом, если это не взлетит.
3 фев 14, 09:28    [15508439]     Ответить | Цитировать Сообщить модератору
 Re: обычная таблица vs. табличная функция в запросе  [new]
Glory
Member

Откуда:
Сообщений: 104760
expimp
В чем причина?

В отсутствии у табличной функции реальной статистики, которая есть у таблицы.
В отсутствии у табличной функции индексов, которые есть у таблицы.
3 фев 14, 10:27    [15508626]     Ответить | Цитировать Сообщить модератору
 Re: обычная таблица vs. табличная функция в запросе  [new]
expimp
Member

Откуда:
Сообщений: 573
Glory,

я упомянул, что возвращает четыре значения. поэтому full scan - единственно возможное действие.
И оно мгновенно, как мне кажется.

Но, спросил то я, что делать? )
Можно ли кроме темп-таблички какой то ход? Можно ли собрать статистику по табличной переменной?
Предложенная выше inline-функция не пойдет - там рекурсивный запрос.

Извиняйте за уровень вопросов. Не обучен. Пока.
3 фев 14, 10:51    [15508764]     Ответить | Цитировать Сообщить модератору
 Re: обычная таблица vs. табличная функция в запросе  [new]
Glory
Member

Откуда:
Сообщений: 104760
expimp
я упомянул, что возвращает четыре значения. поэтому full scan - единственно возможное действие.

Это вы за оптимизатор решаете ?

expimp
Можно ли кроме темп-таблички какой то ход? Можно ли собрать статистику по табличной переменной?

Статистику - нет. Индексы - некотрые

expimp
Предложенная выше inline-функция не пойдет - там рекурсивный запрос.

Рекурсивным вы называете CTE или цикл ?
3 фев 14, 10:53    [15508778]     Ответить | Цитировать Сообщить модератору
 Re: обычная таблица vs. табличная функция в запросе  [new]
expimp
Member

Откуда:
Сообщений: 573
Glory
Рекурсивным вы называете CTE или цикл ?


Вот такая ботва...

ALTER FUNCTION [dbo].[Get_Org_Unit_IDs_Hierarchy]
(
@orgUnitId int
)
RETURNS TABLE
AS
RETURN
(
with orgUnit(ID, org_top_id) as
(
select ID, ORG_TOP_ID
from ORG_ORGANIZATIONAL_UNIT
where ID=@orgUnitId

union all

select ou.ID, ou.ORG_TOP_ID
from ORG_ORGANIZATIONAL_UNIT ou
join orgUnit as u on ou.Id=u.org_top_id
)
select Id
from orgUnit
)
3 фев 14, 12:07    [15509311]     Ответить | Цитировать Сообщить модератору
 Re: обычная таблица vs. табличная функция в запросе  [new]
Glory
Member

Откуда:
Сообщений: 104760
expimp
Вот такая ботва...

Вообще то это и есть inline table-valued function
3 фев 14, 12:10    [15509335]     Ответить | Цитировать Сообщить модератору
 Re: обычная таблица vs. табличная функция в запросе  [new]
expimp
Member

Откуда:
Сообщений: 573
Glory
expimp
Вот такая ботва...

Вообще то это и есть inline table-valued function


Здорово, просто я ухватился за фразу в документации, что inline - это единичный select.

Хорошо, но что сделать, чтобы использование этой функции не увеличивало время выполнения в два раза
по сравнению с таблицей?
3 фев 14, 12:32    [15509498]     Ответить | Цитировать Сообщить модератору
 Re: обычная таблица vs. табличная функция в запросе  [new]
Glory
Member

Откуда:
Сообщений: 104760
expimp
Хорошо, но что сделать, чтобы использование этой функции не увеличивало время выполнения в два раза
по сравнению с таблицей?

Потому что выбирать из таблицы с 4мя записями и фильтровать рекурсивный запрос для получения 4х записей - это разные вещи
3 фев 14, 12:36    [15509522]     Ответить | Цитировать Сообщить модератору
 Re: обычная таблица vs. табличная функция в запросе  [new]
expimp
Member

Откуда:
Сообщений: 573
Glory
Потому что выбирать из таблицы с 4мя записями и фильтровать рекурсивный запрос для получения 4х записей - это разные вещи


Да, но сам запрос выполняется мгновенно!

Так что, единственный вариант - темп таблица?
3 фев 14, 12:44    [15509584]     Ответить | Цитировать Сообщить модератору
 Re: обычная таблица vs. табличная функция в запросе  [new]
Glory
Member

Откуда:
Сообщений: 104760
expimp
Да, но сам запрос выполняется мгновенно!


Вот это вот мгновенно выполняется ?
;with orgUnit(ID, org_top_id) as
(
select ID, ORG_TOP_ID
from ORG_ORGANIZATIONAL_UNIT 
where ID=@orgUnitId

union all

select ou.ID, ou.ORG_TOP_ID
from ORG_ORGANIZATIONAL_UNIT ou 
join orgUnit as u on ou.Id=u.org_top_id
)
select * from orgUnit
    join first_table ft
    join second_table... st
...
...
3 фев 14, 12:47    [15509609]     Ответить | Цитировать Сообщить модератору
 Re: обычная таблица vs. табличная функция в запросе  [new]
edyaN
Member

Откуда:
Сообщений: 185
в ORG_ORGANIZATIONAL_UNIT есть индекс по org_top_id?
3 фев 14, 12:47    [15509613]     Ответить | Цитировать Сообщить модератору
 Re: обычная таблица vs. табличная функция в запросе  [new]
expimp
Member

Откуда:
Сообщений: 573
Glory
expimp
Да, но сам запрос выполняется мгновенно!


Вот это вот мгновенно выполняется ?
;with orgUnit(ID, org_top_id) as
(
select ID, ORG_TOP_ID
from ORG_ORGANIZATIONAL_UNIT 
where ID=@orgUnitId

union all

select ou.ID, ou.ORG_TOP_ID
from ORG_ORGANIZATIONAL_UNIT ou 
join orgUnit as u on ou.Id=u.org_top_id
)
select * from orgUnit
    join first_table ft
    join second_table... st
...
...


select * from orgUnit(777); - вот это - 0.05 сек.
3 фев 14, 13:20    [15509861]     Ответить | Цитировать Сообщить модератору
 Re: обычная таблица vs. табличная функция в запросе  [new]
Glory
Member

Откуда:
Сообщений: 104760
expimp
select * from orgUnit(777); - вот это - 0.05 сек.

А select 1 вообще за тысяные доли секунды выполнятся.
Только вот результат не тот, который нужен.
3 фев 14, 13:23    [15509881]     Ответить | Цитировать Сообщить модератору
 Re: обычная таблица vs. табличная функция в запросе  [new]
expimp
Member

Откуда:
Сообщений: 573
edyaN
в ORG_ORGANIZATIONAL_UNIT есть индекс по org_top_id?


primary key по ID
и nonclustered по ORG_TOP_ID
3 фев 14, 13:26    [15509894]     Ответить | Цитировать Сообщить модератору
 Re: обычная таблица vs. табличная функция в запросе  [new]
expimp
Member

Откуда:
Сообщений: 573
Glory
expimp
select * from orgUnit(777); - вот это - 0.05 сек.

А select 1 вообще за тысяные доли секунды выполнятся.
Только вот результат не тот, который нужен.


)

Но в этом случае именно те четыре значиния, которые и нужны...
3 фев 14, 13:27    [15509901]     Ответить | Цитировать Сообщить модератору
 Re: обычная таблица vs. табличная функция в запросе  [new]
Glory
Member

Откуда:
Сообщений: 104760
expimp
Но в этом случае именно те четыре значиния, которые и нужны...

Если это тот результат, что вам нужен, то зачем вы тогда делаете еще какие-то join-ы ?
3 фев 14, 13:28    [15509909]     Ответить | Цитировать Сообщить модератору
 Re: обычная таблица vs. табличная функция в запросе  [new]
expimp
Member

Откуда:
Сообщений: 573
Glory
Если это тот результат, что вам нужен, то зачем вы тогда делаете еще какие-то join-ы ?


Это чужой запрос. Штатно в запросе табличная функция, возвращающая четыре значения.
С этой табличной функцией join'ятся еще полдюжины уже обычных таблиц.
Если вместо табличной функции указать обычную таблицу, в которую заранее записать значения функции,
весь запрос работает в два раза быстрее. Число чтений в десять раз меньше, ЦПУ меньше в три раза.
Пытаюсь понять причины - ведь сама функция отдельно выполняется небрежимо быстро.
Но просто так заменить функцию на таблицу - не гламурно. Ищу правильное решение.
3 фев 14, 14:16    [15510349]     Ответить | Цитировать Сообщить модератору
 Re: обычная таблица vs. табличная функция в запросе  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
expimp,

А вы, когда замеры делаете, включаете время на заполнение временной таблицы? =)

In-line табличная функция и запрос, в котором она используется, рассматривается оптимизатором как единое целое при построении плана. Т.е., грубо говоря, текст функции «подставляется» в запрос и вся эта конструкция оптимизируется целиком.

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

Кроме этого, для рекурсивных выражений оптимизатор использует специальную конструкцию с применением спулов, ниже картинка из блога Rob Farley (посмотрите на план вашего запроса, наверняка что-то похожее увидите):
+ Recursive CTE Plan Example
Картинка с другого сайта.


Представьте, что эта ветка плана оказалась на внутренней стороне Nested Loops Join, и выполняется несколько раз, вместо того, чтобы выполнится один раз, когда вы явно материализуете результат. Так может быть, например, из-за ошибки в оценке, неправильного выбора типа или порядка соединения, и т.д. – куча вариантов, вплоть до того, что просто такая форма плана получилась в результате преобразования дерева операторов, и этот план по-мнению оптимизатора - достаточно хорош.

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

Если вас устраивает решение с временной таблицей, но просто хочется «покрасивее», то забейте, оставьте таблицу. Если есть желание разбираться, то анализируйте планы.
3 фев 14, 14:19    [15510364]     Ответить | Цитировать Сообщить модератору
 Re: обычная таблица vs. табличная функция в запросе  [new]
Glory
Member

Откуда:
Сообщений: 104760
expimp
Если вместо табличной функции указать обычную таблицу, в которую заранее записать значения функции,
весь запрос работает в два раза быстрее. Число чтений в десять раз меньше, ЦПУ меньше в три раза.

Почему вы тогда сравниваете производительность разных запросов ?

expimp
Но просто так заменить функцию на таблицу - не гламурно.

Так вам гламур или производительность нужна ?

expimp
Ищу правильное решение.

Добиваться совпадения планов выполнения
3 фев 14, 14:20    [15510371]     Ответить | Цитировать Сообщить модератору
 Re: обычная таблица vs. табличная функция в запросе  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
expimp,

а если в SELECTе сразу написать готовые значения полей
(с помощью конструктора табличных значений, например),
то выигрыш будет в миллиарды раз!
3 фев 14, 14:23    [15510399]     Ответить | Цитировать Сообщить модератору
 Re: обычная таблица vs. табличная функция в запросе  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
Glory и SomewhereSomehow, а если автор заменит инлайн табличную функцию на НЕинлайн? Это чисто теоретически не должно заставить ее выполниться отдельно от общего плана и явиться аналогом перекладки во времянку?
13 апр 14, 22:40    [15876305]     Ответить | Цитировать Сообщить модератору
 Re: обычная таблица vs. табличная функция в запросе  [new]
aleks2
Guest
Mike_za
Glory и SomewhereSomehow, а если автор заменит инлайн табличную функцию на НЕинлайн? Это чисто теоретически не должно заставить ее выполниться отдельно от общего плана и явиться аналогом перекладки во времянку?

Да.
С оговоркой: ОДИН вызов multistatment-табличной ~= одному использованию временной таблицы в этом месте. Два вызова - ДВЕ таблицы.

Не забудь индекс на возвращаемой таблице сделать.
14 апр 14, 06:00    [15876980]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить