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

Откуда:
Сообщений: 201
Есть функция, возвращающая табличное значение. Параметры функции - int идентификаторы даты вида ггггммдд.

Выполняю запрос такой:
declare
	@d1 int = dbo.OLAP_GetPartitionIntervals(N'2016y_1hy', N'MIN'),
	@d2 int = dbo.OLAP_GetPartitionIntervals(N'2016y_1hy', N'MAX')
	
SELECT * FROM dbo.my_function(@d1, @d2)

Время выполнения - 2 секунды

Выполняю запрос такой:
SELECT * FROM dbo.my_function(dbo.OLAP_GetPartitionIntervals(N'2016y_1hy', N'MIN'), dbo.OLAP_GetPartitionIntervals(N'2016y_1hy', N'MAX'))

Время выполнения - 9 секунд

Сама функция dbo.OLAP_GetPartitionIntervals простая, результат возвращает мгновенно.
Почему такая разница во времени в зависимости от способа подачи параметров?
26 апр 16, 03:46    [19105915]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
Max_11111
Member

Откуда:
Сообщений: 201
планы выполнения

К сообщению приложен файл (plan.zip - 23Kb) cкачать
26 апр 16, 03:47    [19105916]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
Barkan
Member

Откуда: Дремучее заМКАДье
Сообщений: 504
Max_11111,

Сниффинг (вот не ручаюсь что правильно обозвал это мероприятие) параметров в функции делал?
26 апр 16, 05:20    [19105933]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
Max_11111
Member

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

Почитал, посмотрел что можно сделать. Решения пока не нашёл. Варианты такие:
1. объявлять в запросе параметры не вариант, т.к. я этот запрос использую при построении секции olap куба, а он автоматически из моего запроса делает подзапрос вида
select * from (мой запрос) as q
. Поэтому на параметры ругается.
2. если я переопределю параметры в самой функции, то время выполнения просядет ещё более жёстко, т.к. сейчас у меня Inline-функция, а будет Multistatement.
3. Использования опций
option (optimize for UNKNOWN)
,
option (recompile)
время выполнения не изменило.

Что ещё можно сделать?
26 апр 16, 06:33    [19105966]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
Max_11111
Member

Откуда:
Сообщений: 201
Параметры функции "dbo.my_function" - границы определённого периоды. Если я знаю все границы периодов, знаю примерное количество строк в этих периодах, это может мне как-то помочь? Какие хинты можно попробовать применить?
26 апр 16, 06:39    [19105968]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
Glory
Member

Откуда:
Сообщений: 104751
Max_11111
Почему такая разница во времени в зависимости от способа подачи параметров?

Это не способ передачи параметров.
"Сложение" трех разных запросов в один означает то, что время общего запроса будет равно времени самого быстрого из запросов

Max_11111
Что ещё можно сделать?

Использовать
declare
@d1 int = dbo.OLAP_GetPartitionIntervals(N'2016y_1hy', N'MIN'),
@d2 int = dbo.OLAP_GetPartitionIntervals(N'2016y_1hy', N'MAX')

SELECT * FROM dbo.my_function(@d1, @d2)

или вам этого мало ?
26 апр 16, 08:31    [19106089]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
iljy
Member

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

переделайте OLAP_GetPartitionIntervals в inline, после чего перепишите запрос так:

SELECT * FROM dbo.OLAP_GetPartitionIntervals(N'2016y_1hy', N'MIN') a(val)
   CROSS JOIN dbo.OLAP_GetPartitionIntervals(N'2016y_1hy', N'MAX') b(val)
   CROSS APPLY dbo.my_function(a.val, b.val)
26 апр 16, 08:34    [19106091]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
Max_11111
Member

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

Такой вариант не работает, т.к. OLAP мой запрос преобразует во вложенный: select * from (мой запрос) as q
26 апр 16, 09:26    [19106231]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
Glory
Member

Откуда:
Сообщений: 104751
Max_11111
т.к. OLAP мой запрос преобразует во вложенный: select * from (мой запрос) as q

Что такое "OLAP" ? Клиентская программа такая ?
26 апр 16, 09:28    [19106245]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
Max_11111
Member

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

Увы, но время выполнения составило 23 секунды. Это даже хуже текущего результата
26 апр 16, 09:28    [19106247]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
TaPaK
Member

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

Увы, но время выполнения составило 23 секунды. Это даже хуже текущего результата

вы точно её переделали в инлайн???
26 апр 16, 09:31    [19106259]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
Max_11111
Member

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

Точно, точно. До этого функция была скалярной, а "SELECT * FROM СкалярнаяФункция" не работает
26 апр 16, 10:40    [19106558]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
Max_11111
Member

Откуда:
Сообщений: 201
Glory
Что такое "OLAP" ? Клиентская программа такая ?


OLAP-куб, для работы с ним предназначен компонент SQL Server Analysis Services. Многомерная база.
26 апр 16, 10:44    [19106598]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Max_11111
TaPaK,

Точно, точно. До этого функция была скалярной, а "SELECT * FROM СкалярнаяФункция" не работает
Этот SELECT работает и для табличных многооператорных функций.
Но это совсем, совсем не то
26 апр 16, 10:44    [19106600]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
Glory
Member

Откуда:
Сообщений: 104751
Max_11111
OLAP-куб, для работы с ним предназначен компонент SQL Server Analysis Services. Многомерная база.

И что внутри этого куба вы пишите TSQL код ?
26 апр 16, 10:45    [19106610]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
Max_11111
Member

Откуда:
Сообщений: 201
iljy
переделайте OLAP_GetPartitionIntervals в inline, после чего перепишите запрос так:
SELECT * FROM dbo.OLAP_GetPartitionIntervals(N'2016y_1hy', N'MIN') a(val)
CROSS JOIN dbo.OLAP_GetPartitionIntervals(N'2016y_1hy', N'MAX') b(val)
CROSS APPLY dbo.my_function(a.val, b.val)


Немного обманываю. Это первый раз 23 секунды. последующие разы выполняется за 10 секунд, что тоже неприемлемо.
26 апр 16, 10:47    [19106617]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Max_11111
iljy
переделайте OLAP_GetPartitionIntervals в inline, после чего перепишите запрос так:
SELECT * FROM dbo.OLAP_GetPartitionIntervals(N'2016y_1hy', N'MIN') a(val)
CROSS JOIN dbo.OLAP_GetPartitionIntervals(N'2016y_1hy', N'MAX') b(val)
CROSS APPLY dbo.my_function(a.val, b.val)


Немного обманываю. Это первый раз 23 секунды. последующие разы выполняется за 10 секунд, что тоже неприемлемо.

может покажете как вы переделали в инлайновую, результат неоправданно завышен
26 апр 16, 10:57    [19106703]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
invm
Member

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

Хватит уже партизанить. Показывайте текст dbo.OLAP_GetPartitionIntervals и план запроса.
26 апр 16, 10:58    [19106710]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
Max_11111
Member

Откуда:
Сообщений: 201
Glory
И что внутри этого куба вы пишите TSQL код ?

Куб состоит из мер и измерений. Данные не в них откуда-то берутся. На вход я подаются SQL-запросы, и при обработке OLAP куба выполняются SQL запросы для получения данных. при этом все запросы преобразуются в подзапросы и получаются в итоге запросы вида
select * from (мой запрос) as q

Для заполнения одной из мер я и написал запрос, для которого случайно нашёл проблемы с производительностью.
Меру я секционировал по дате, получилось около 10 секций. Чтобы не указывать в запросе для каждой секции конкретные даты я создал специальную функцию, эти даты возвращающую. И, если я захочу завтра изменить периоды секционирования, я могу просто изменить их в одном месте, а не бегать по всем секциям и менять
26 апр 16, 11:01    [19106738]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
Max_11111
Member

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

функция:
CREATE FUNCTION [dbo].[OLAP_GetPartitionIntervals_Inline]
(	
	@PartitionName nvarchar(50),	-- Имя секции или ALL
	@Value nvarchar(50)				-- MIN/MAX граница
)
RETURNS TABLE 
AS
RETURN 
(
	SELECT
		CASE
			WHEN @Value = 'MIN' THEN MIN(DateMinID)
			WHEN @Value = 'MAX' THEN MAX(DateMaxID)
			ELSE NULL
		END AS DateID
	FROM
		dbo.OLAP_Partitions
	WHERE
		@PartitionName = Name
		OR @PartitionName = 'ALL'
)


запрос:
SELECT * FROM dbo.OLAP_GetPartitionIntervals_Inline(N'2016y_1hy', N'MIN') a
   CROSS JOIN dbo.OLAP_GetPartitionIntervals_Inline(N'2016y_1hy', N'MAX') b
   CROSS APPLY dbo.my_function(a.DateID, b.DateID)


К сообщению приложен файл (inline.zip - 18Kb) cкачать
26 апр 16, 11:06    [19106779]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
TaPaK
Member

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

на всякий случай: а почему за раз Min/Max не получить??
26 апр 16, 11:12    [19106818]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
Max_11111
Member

Откуда:
Сообщений: 201
TaPaK
а почему за раз Min/Max не получить??

потому что изначально была скалярная функция, не заморачивался с переделкой =)
Переделал - результат тот же самый. 10 секунд
26 апр 16, 11:17    [19106848]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Max_11111,
ну и там OPTION(RECOMPILE) в основной запрос, для чистоты эксперимента
26 апр 16, 11:19    [19106874]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
Max_11111
Member

Откуда:
Сообщений: 201
TaPaK
OPTION(RECOMPILE) в основной запрос

В my_function, или в мой многострадальный select? в select - ничего не поменялось, в саму функцию не вставляется - ругается на синтаксис
26 апр 16, 11:23    [19106910]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
TaPaK
Member

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

я как то не верю, что ничего не меняется :)

SELECT *
FROM
dbo.OLAP_GetPartitionIntervals(N'2016y_1hy') a
CROSS APPLY
dbo.my_function(a.MinVal, a.MaxVal)
OPTION(RECOMPILE)
26 апр 16, 11:26    [19106935]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить